w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Stop converting numbers to date while importing text file using VBA in excel
Run below Sub before importing txt files Sub Prep() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets ws.Cells.NumberFormat = "@" Next End Sub this sub iterates through all sheets and formats all cells to text, so imported values will appear in original format.

Categories : Excel

how to stop android emulator from automatically turning off
Did you turn on a Sleep|Hibernate|Powerdown setting on that AVD's settings, or perhaps are they are set on your AVD by default to powerdown? It would persist everytime you start that AVD, unless you tell the AVD Manager to wipe all previous settings every time it launches. You might try recreating a new AVD instance and deleting the current one. EDIT: New Pictures * Now that you set up an AVD, and went inside the emulator to turn off|delay the "sleep|suspend" mode, you need to make sure the AVD manager never overwrites this setting when you start that AVD the next time. From the AVD manager, if you click start, it will show you this dialog, make shure "wipe user data" is unchecked. And to make sure the Eclipse isn't wiping out your AVD settings when you launch an emulator with the "R

Categories : Android

short mysql dates array to readable
You can loop through array and add nth values to new array that You can use in Your graph. You can use modulo on array keys for that and use ratio of values to number of results You whant. $new_array = array(); $array = array(1,2,3,4,5,6,7,8,9,7,8,9,6,5); $array_count = count($array); $number_of_results = 5; // number of results you whant $ratio = ceil($array_count / $number_of_results); foreach ($array as $key => $val) { if ( $key % $ratio == 0 ) { $new_array[] = $val; } }

Categories : PHP

How does excel convert Julian dates to calender dates?
41348 is not julian date. Excel needs to use a formula to convert a julian date to a proper date. 183349 is an example of a julian date. 41348 is simply the number of days passed after 01/01/1900. You can add those number of days to 01/01/1900, if I recall correctly, in SQL server to get the desired results. EDIT: Yup, use DATEADD. Add a column with 01/01/1900 UPDATE Table SET NewDate = DATEADD(Days, RawDate-2, '01/01/1900') You need to remove 1 from the RawDate since 1 corresponds to 01/01/1900 and another 1 apparently I'm not sure where, but investigating. On my SQL Server2008R2, apparently it considers 1900 to be a non-leap year. There goes the additional 1 to remove!

Categories : Sql Server

Turning an excel formula into a VBA function
Here is a simple solution... Building_name = ??? Date = ??? Last_Row = Range("A65536").End(xlUp).Row For i = 1 To Last_Row if cells(i,1).value = Building_Name Then if date >= cells(i,4).value Then if date <= cells(i,5).value Then first instance = cells(i,6).value end if end if end if next you should add a test at the end to avoid the case where there is no first instance in the table

Categories : Excel

Why does range in python "stop short"?
This is just how python's range works. Quote from docs: This is a versatile function to create lists containing arithmetic progressions. It is most often used in for loops. The arguments must be plain integers. If the step argument is omitted, it defaults to 1. If the start argument is omitted, it defaults to 0. The full form returns a list of plain integers [start, start + step, start + 2 * step, ...]. If step is positive, the last element is the largest start + i * step less than stop; if step is negative, the last element is the smallest start + i * step greater than stop. step must not be zero (or else ValueError is raised).

Categories : Python

Turning a python dict. to an excel sheet
clubs is a list of dictionaries, whereas each dictionary has four fields: name, address, number, and Subway. You will need to encode each of the fields: # Instead of: #dict_writer.writerow([unicode(s).encode("utf-8") for s in clubs]) # Do this: for c in clubs: # Encode each field: name, address, ... for k in c.keys(): c[k] = c[k].encode('utf-8').strip() # Write to file dict_writer.writerow(c) Update I looked at your data and some of the fields have ending new line , so I updated the code to encode and strip white spaces at the same time.

Categories : Python

Is there a way to run a short callback when closing/stop debugging a c# application in VS?
I dont know how to do it. But if you dont find a way to do it, here is an advice: Use a virtual machine to debug your program. Even a BSOD occur, you will just have to reboot your virtual machine instead of your real pc. It really helps when working on kernel/driver development.

Categories : Visual Studio

Excel 2010 comparing multiple columns (2 columns to 2 other columns)
Here is a User Defined Function that will perform a 2 column lookup. Treat it like a vlookup. LookupPair is a pair of cells. (C2:D2) in your example. Anything other that a pair of side-by-side cells will cause an error. LookupRange is the columns that include both the matching pair columns and the return column. Something like (I1:K101) in your example. LookupRange must contain at least two columns or an error is generated. ReturnCol is the column number in LookupRange that contains the value to be returned. (same as Col_index_num in a vlookup). The function only does exact matches. Function DoubleColMatch(LookupPair As Range, LookupRange As Range, ReturnCol As Integer) As Variant Dim ReturnVal As Variant Dim Col1Val As Variant Dim Col2Val As Variant Dim x As Long If LookupPair.R

Categories : Excel

Extracting some numbers from a short HTML snippet using regular expressions
Use re.findall, >>> re.findall(r"d+", content) ['28', '1'] But you may want to narrow down your regex. EDIT: You may want to change your regular expression to some variation of r"<span.*?>.*?(d+).*?</span>" to match only numbers inside span tags.

Categories : Python

In Excel, is there an early opt out AND/OR functions (Short Circuit Evaluation)?
What you're calling "early opt out" is more commonly called "short-circuit evaluation," and is generally a feature of languages in the C/C++/C#/Java family (but, notably, not Visual BASIC). For Excel formulas, some logical functions practice short-circuit evaluation but some do not. AND does not, as you've found. Neither does OR--if the first argument in an OR expression is true, Excel will still try to evaluate the subsequent arguments. The solution is to use nested IFs; evaluation of IFs goes step-by-step from outer to inner, branching as necessary, and stopping when there is no further nested formula to be evaluated. This produces the correct short-circuit behavior. So you can write your code above as =IF(FALSE, IF(ISNA(<address of cell to test for the #N/A state>), TRUE),

Categories : Excel

Excel VBA: hide/unhide columns in day-over-day tracker, skipping weekend columns
If your data has a row of Dates, you can indeed use Weekday(Cells(r,c)) to determine if its a weekend, then use Select Case to hide/unhide columns. The current can be retrieved by Date(). Then you can set to codes in Sub Workbook_Open() in ThisWorkbook object. So when the file is opened, it runs the code so today and 9 previous days are not hidden. EDIT: Add these 2 sub into "ThisWorkbook" object, change as per your sheet name, row and column. If you are to want 9 previous "Weekdays", then you will have to change the Case or use different approach to determine whether a column should be hidden or not. This is as far as I will go. Good luck! Sub Workbook_Open() ShowTodayPlusPrevious End Sub Private Sub ShowTodayPlusPrevious() ' Assuming Row 1 contains the dates, stating from co

Categories : Vba

How to read dates in R through numbers
It is not clear from your question what the numbers in your file represent. Dates can be expressed as number of days since some fixed day. Suppose it is 1970-01-01, then you can use: as.Date(17383, origin="1970-01-01")

Categories : R

Turning a Data.Frame with 28441 Rows in it to a Dataframe with 28 Rows and 1000 Columns in R
Not sure what language this is coded in but I think I understand your problem. If your original is all in the first column of zip.codes(I assume this is the old dataframe), you want [i,1] meaning row i, first column. Your j should go from 1 to for (i in 1:length(zip.codes)) { k = i % 1000 for(j in 1:1000) { new.zip.codes[k,j]<-zip.codes[i,1] } } Using the % as a modulo operator, you can pick out the row number you want, had have j as your column control. This is extremely useful when you know your fixed table size. Hope this helps.

Categories : R

Multi dates in columns
SQL92 has the function EXTRACT Its usage is select EXTRACT(DAY FROM import_date) So I guess you could do select 'WE' + EXTRACT(DAY FROM import_date) + '/' + EXTRACT(MONTH FROM import_date) from IPSHARE.HRData

Categories : SQL

convert specified columns to dates in R
Data frames are lists. Use lapply: df[,-1] <- lapply(df[,-1],as.Date,origin = "1970-01-01") > df names date_1 date_2 date_3 1 tom 2011-06-02 2011-06-30 2011-07-30 2 dick 2011-03-01 2011-03-29 2011-04-27 3 harry 2011-03-01 2011-03-31 2011-04-28 4 mary 2011-06-16 <NA> <NA> 5 susie 2011-02-08 2011-03-08 2011-04-05

Categories : R

Parsing String Mixed with HTML, Words, Numbers, and Dates
This seems like a lot of work when you could use regular expressions. See here and here for a good starting point. If you add a reference to "Microsoft VBScript Regular Expressions 5.5" and add the following functions (I've included a few more functions than necessary, in case they're useful elsewhere): Public Function RegEx(strInput As String, strRegEx As String, Optional bIgnoreCase As Boolean = True, Optional bMultiLine As Boolean = False) As Boolean Dim RegExp As VBScript_RegExp_55.RegExp Set RegExp = New VBScript_RegExp_55.RegExp With RegExp .MultiLine = bMultiLine .IgnoreCase = bIgnoreCase .Pattern = strRegEx End With RegEx = RegExp.test(strInput) Set RegExp = Nothing End Function Public Function RegExMatch(strInput As String, strRe

Categories : Vba

Writing to excel and dates
I found a trick that works. Adding a blank space in front of the cell value, the conversion is not performed in case of varchar: mysheet.Cells[rowCount, i] = " " + drow[i - 1].ToString();

Categories : C#

How to split dates with VB in Excel
Theory: Check the length of the cell. If the cell is longer than 10 characters, use the SPLIT function to get the 2 dates. Set the months equal to a variable, and do a loop based on those months to calculate the dates between them. You would probably store those dates in an array. Then write the array to the spreadsheet and move to the next cell to start the process over. EDIT: Sub prSplit() If len(ActiveCell.Value) > 10 Then Dim arr() As String arr = Trim(Split(ActiveCell.Value, "-")) For i = LBound(arr) To UBound(arr) MsgBox arr(i) Next End If End Sub You can start with this and tweak the code until you get it. I just don't have the time to do the whole thing. Sorry. :o(

Categories : Excel

how to get data between same dates with two different date columns
You can look for rows that have overlapping dates using an exists clause: select * from ind_emp_leaves iel where exists ( select 1 from ind_emp_leaves iel2 where not (iel2.leavetodate < iel.leavefromdate or iel2.leavefromdate > iel.leavetodate) and iel2.rowid != iel.rowid ); USERNAME LEAVEFROMDATE LEAVETODATE ---------- ------------- ----------- dhinesh 05-SEP-13 08-SEP-13 raju 08-SEP-13 11-SEP-13 Or if you want to know about the overlap, not just that it exists, you can use an outer join: select iel.username, iel.leavefromdate, iel.leavetodate, iel2.username as overlaps from ind_emp_leaves iel left join ind_emp_leaves iel2 on not (iel2.leavetodate < iel.leavefromdate or iel2.leavefromdate > iel.leavetodate) and iel2.rowid != iel.rowi

Categories : Oracle

Finding columns which contain dates in Pandas
If you are looking to convert entire columns, you can use convert_objects: df.convert_objects(convert_dates=True) To extract dates contained in columns/Series you could use findall: In [11]: s = pd.Series(['1', '10/11/2011 11:11']) In [12]: s.str.findall('d{2}/d{2}/d{4} d{2}:d{2}') Out[12]: 0 [] 1 [10/11/2011 11:11] dtype: object In [13]: s.str.findall('d{2}/d{2}/d{4} d{2}:d{2}').apply(pd.Series) Out[13]: 0 0 NaN 1 10/11/2011 11:11 *and then convert to Timestamps using convert_objects...*

Categories : Python

Excel filter groups some dates but not others
my best guess is that all the dates in the other tabs might be stored as text and if so, you can convert them back to the date format by copying 1 and pasting as Multiply (Paste Special > Multiply) to all the dates.

Categories : Excel

Excel Function Difference Between Two Dates
Change time() with timeserial() Function DIFERENCAHORASMINUTOS(ByVal EntryTime As Date, ByVal ExitTime As Date) Dim hexit, hentry, mexit, mentry, dminutes, dhours As Integer hexit = Int(Hour(ExitTime)) hentry = Int(Hour(EntryTime)) mentry = Int(Minute(EntryTime)) mexit = Int(Minute(ExitTime)) If ExitTime < EntryTime Then If mexit < mentry Then dhours = (hexit + 24) - hentry dminutes = (mexit + 60) - mentry DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0) Else dhours = (hexit + 24) - hentry dminutes = mexit - mentry DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0) End If Else If mexit < mentry Then dhours = hexit - hentry dminutes = (mexit + 60) - mentry DIFERENCAHORASMINUTOS = Ti

Categories : Excel

Lookup a value from a range of dates in excel
Although it is a bit complex requirement it could be done with built-in functions (although it can look a bit obscure :-) ). Specifically I mean function SUMIFS. It has several parameters. The first one is an area with values to be summed. It is B8:B12 in this example. The second is an area whith values to be checked with some condition. It is A8:A12. The third is a criterion to be applied for area from second parameter. It is (inter alia) account #. So the formula says: sum all values from rows in B8:B12 where account # (A8:A12) is equal to desired account # (e.g. A3). Ok, it is not all, you need specify the time range. It would be a bit clunky because you must check if two time period are overlapping (it would be easier to check if one date is in specified period). But it could

Categories : Vb.Net

How to autoupdate the excel dates using java
You can call a java function that returns the current date and write it to the excel file using jxl. To make it automated visit this http://www.advancedinstaller.com/java.html. Set this to auto start service. I guess that will solve your problem.

Categories : Java

Tallying dates and times in excel
Not knowing the details, I would try to use a PivotTable. There is the ability to group by dates; see this page for details (including a fancy animation). Some additional searching should turn up a lot of resources on PivotTables; they are very powerful.

Categories : Excel

replicating times across set of dates in excel
If you're going to do this once, you can try this: First, put in the data for 9/1 as you have. Then enter this formula into the next cell (assuming data starts in A1): =A1+1 Then select that cell with the formula and drag the formula down until you reach 9/30/2013 22:00 (row 150 if data starts in A1) Then, copy all of the data and Paste Special->Values Only into the same range.

Categories : Excel

How to stop adding numbers that occur twice in C#
Not the most efficient way, but should work var sum = 0; for(int i=0;i<1000;i++) { if(i%3==0||i%5==0) //checks if something is multiple of 3 or 5 sum+=i; // sums only when it's multiple of 3 or 5 } It ommits situations where something is multiple of 3 and 5. Takes each number once. One line linq way: var sum = Enumerable.Range(3, 1000).Sum(x => (x % 3 == 0 || x % 5 == 0) ? x : 0); Fastest mathematical approach version: var result = SumDivisbleBy(3,999)+SumDivisbleBy(5,999)-SumDivisbleBy(15,999); private int SumDivisbleBy(int n, int p) { return n*(p/n)*((p/n)+1)/2; } it calculates sum of all numbers divisible by 3 and 5 then substracts sum of numbers divisible by 15. Explanation: http://www.wikihow.com/Sum-the-Integers-from-1-to-N

Categories : C#

Program will stop once five even numbers are placed in array
#include <stdio.h> #include <conio.h> int main() { int x, even[5], numEven = 0; while (numEven < 5) { scanf("%d", &x); if (x % 2 == 0) { even[numEven++] = x; } } printf(" Even numbers: "); //printing even numbers for(x=0; x<numEven; x++) { printf("%d, ", even[x]); } getch(); return 0; } You keep readin inputs till numEven reaches 5. If the read input is an even number store it in the array and increment numEven.

Categories : C

Excel COUNTIFS with mixed dates and logicals
You just need all ranges to be the same size. Your first range starts at row 1 the others start at row 2 - if you make them all start at row 2 it should work OK =COUNTIFS($O$2:$O$601,"=Yes",$D$2:$D$601,"<September 9, 2013 20:00",$D$2:$D$601,">September 9, 2013 17:45")

Categories : Excel

Excel 2010 - change US dates to UK format
I'm assuming that the date you received is formatted as text and that simply formatting it as date is not changing anything. You can run the following formula on the date: =(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"-"&LEFT(A1,FIND("/",A1)-1)&"-"&RIGHT(A1,4))*1 If you get numbers, you just need to format it as dd/mm/yyyy and it should be good.

Categories : Excel

Excel vba .Find() function on a list of dates which appear as #
Change the parameter LookIn:=xlvalues to LookIn:=xlFormulas Sub Macro1() 'Will find dates showing ##### where column width is not wide enough Set a = Sheet1.Range("A:A").Find(What:=#8/22/2013#, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Debug.Print a.Address 'Error on dates showing ##### where column width is not wide enough Set a = Sheet1.Range("A:A").Find(What:=#8/22/2013#, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Debug.Print a.Address End Sub

Categories : Excel

VBA Excel autofilter dates in month period
Try Criteria2, this is a bit cryptic though. Pass Array(1, datestring), where 1 will filter the entire month of that date, and the datestring must be in m/d/y format. (0 = entire year, 2 = day, and some more, as discussed somewhere here). Dim s As String s = Format(Now, "MM""/""dd""/""yyyy") ActiveSheet.Range("$A$29:$CG$3582").AutoFilter _ Field:=18, Operator:=xlFilterValues, _ Criteria2:=Array(1, s)

Categories : Excel

Excel- Average days between group of dates
If your dates are in ColumnB: =(MAX(B:B)-MIN(B:B)--1)/COUNT(B:B) or: =(MAX(B:B)+1-MIN(B:B))/COUNTA(B:B) should serve. Edit If you have multiple parts (the above assumed only one) and the list is in no particular order then a PivotTable may be best (say with its top left-hand corner in D1), in Tabular form with Part for Row Labels and Delivery three times for Σ Values (the first as MAX, the second as MIN and the third as COUNT). Then =(1+E3-F3)/G3 copied down should give you the average bumber of days between deliveries. For example 5 in your example (3 deliveries in 15 days).

Categories : Excel

Excel formula to calculate intersected dates?
Assuming you are counting all days within the periods, including Saturdays and Sundays you can use this "array formula" as per my screenshot below: Formula is as follows in D3: =SUM(IF(A$8:A$10=A3,IF(C$8:C$10>=B3,IF(B$8:B$10<=C3,IF(C$8:C$10>C3,C3,C$8:C$10)-IF(B$8:B$10<B3,B3,B$8:B$10)+1)))) confirmed with CTRL+SHIFT+ENTER and copied down to D4 This may look like overkill for your small example but I'm assuming your real data is larger - this solution can be extended as required, even for multiple unsorted vacation periods

Categories : Excel

Selecting data for columns based on a range of dates
You might consider using the PIVOT command: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx OR If you are okay with the result being a comma separated list, you could use the STUFF and FOR XML commands like so. SELECT DISTINCT style_name, STUFF( (SELECT ',' + CAST(net_sales AS VARCHAR(20)) FROM MyTable AS SubTable WHERE SubTableUser.style = MyTable.style_name ORDER BY week_id DESC --DESC will get your max ID to be first FOR XML PATH('')), 1, 1, '') AS net_sales_list FROM MyTable ORDER BY style_name This will provide you with: style_name | net_sales_list --------------------------- ABCD | 100.00,125.00 EFGH | 75.00,50.00

Categories : SQL

Is there a better way to write this Oracle SQL that deals with comparing dates in two different columns?
There is a more "modern" way to do this, using the analytic LEAD function. I think it's better because it's more compact, plus once you get used to the analytic functions you'll find them easy to read - but that's just an opinion :) SELECT Start_Date_Time, End_Date_Time, ROUND((next_start_date - end_date_time)*24,2) hours_to_next_start FROM ( SELECT Start_Date_Time, End_Date_Time, LEAD(Start_Date_Time) OVER (ORDER BY Start_Date_Time) AS Next_Start_Date FROM myTable ) ORDER BY Start_Date_Time There's a SQL Fiddle here. To get more insight into how this works, try running the inner query on its own.

Categories : SQL

Insert Columns between columns in excel
reposting the above comment as an answer so question may be marked as answered. Please see: Adding a new column at the start of an excel table in an excel for solution. All you need do is change the "A1" value to the column you wish to insert before ("D1" in your example)

Categories : C#

Excel If Statement finding number of days between two dates
Try EDATE() function =Edate(A1,1)-B1 Reference .. http://office.microsoft.com/en-001/excel-help/edate-function-HP010342465.aspx?CTT=5&origin=HP010342402

Categories : Excel

Exporting mySQL to Excel with Dates in Desired Format
Change your SQL to: Select id, name, id_continent, date_format(lastvisit,'%m/%d/%Y'),date_format(cdate,'%m/%d/%Y'), date_format(ddate,'%m/%d/%Y'),notes from $DB_TBLName ORDER BY ddate ASC";

Categories : PHP



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.