Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format converted from UK to US format
I have a workbook with multiple tabs, each of which take data from mutlipe
spreadsheets using a Query. I then use the code below, to consolidate all the data on the tabs into one table, so i can PivotReport on it. The problem comes from the columns with dates in it - they import using the Query in UK format, but after the code has brought the tabs into one, they become US format as text. Can anyone help correct this, as i'm having to use a workaround formula to rearrange the cells at the moment. Sub collate2() Dim firstRow As Double, lastRow As Double, srcRng As Range, destRng As Range, ws As Worksheet firstRow = 2 For Each ws In Sheets 'goes through the workbook grabbing data, if the sheet isn't called "orders" With ws If LCase(.Name) < "analysis" Then 'finds last used row by selecting last row and simulating ctrl-up arrow lastRow = .Cells(Rows.Count, "B").End(xlUp).Row If lastRow = firstRow Then 'makes sure there's some data to copy 'gets range of data in current sheet Set srcRng = .Range(.Cells(firstRow, "A"), ..Cells(lastRow, "AL")) 'gets range of first cell on row below data already on sheet Set destRng = Sheets("MasterSheet").Cells(Rows.Count, "B").End(xlUp).Resize(lastRow - firstRow + 1, 40).Offset(1, -1) destRng.Value = srcRng.Value End If End If End With Next ws End Sub -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format converted from UK to US format
If they are coming in as "text", then what do these dates look like as
"text"... 12/8/2009, December 8 2009, 8Dec09, something else? I would think the plan could be to parse the date text you have (after knowing its format) into its month, day and year and put those pieces into the proper argument location in the DateSerial function to create a date in you given system, then assign that date to your cell. -- Rick (MVP - Excel) "Richard" wrote in message ... I have a workbook with multiple tabs, each of which take data from mutlipe spreadsheets using a Query. I then use the code below, to consolidate all the data on the tabs into one table, so i can PivotReport on it. The problem comes from the columns with dates in it - they import using the Query in UK format, but after the code has brought the tabs into one, they become US format as text. Can anyone help correct this, as i'm having to use a workaround formula to rearrange the cells at the moment. Sub collate2() Dim firstRow As Double, lastRow As Double, srcRng As Range, destRng As Range, ws As Worksheet firstRow = 2 For Each ws In Sheets 'goes through the workbook grabbing data, if the sheet isn't called "orders" With ws If LCase(.Name) < "analysis" Then 'finds last used row by selecting last row and simulating ctrl-up arrow lastRow = .Cells(Rows.Count, "B").End(xlUp).Row If lastRow = firstRow Then 'makes sure there's some data to copy 'gets range of data in current sheet Set srcRng = .Range(.Cells(firstRow, "A"), .Cells(lastRow, "AL")) 'gets range of first cell on row below data already on sheet Set destRng = Sheets("MasterSheet").Cells(Rows.Count, "B").End(xlUp).Resize(lastRow - firstRow + 1, 40).Offset(1, -1) destRng.Value = srcRng.Value End If End If End With Next ws End Sub -- Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format converted from UK to US format
Just to add to Rick's notes...
The problem I've had with stuff like this is that some dates come as text--and others come in as dates--just the wrong dates. 12/31/2009 looks like a date in my USA settings and is brought in as Dec 31, 2009. 31/12/2009 doesn't look like a date in my USA settings and is brought in as text. But the problem is these kind of things... 06/07/2009 It looks like a date and is brought in as Jun 7, 2009. But the real value for this date is July 6, 2009. So if I convert the "text dates" to real dates, I've still got all those real dates that don't reflect what they should. If it's at all possible, I'd try to bring this data in as text--and then parse it myself. Richard wrote: I have a workbook with multiple tabs, each of which take data from mutlipe spreadsheets using a Query. I then use the code below, to consolidate all the data on the tabs into one table, so i can PivotReport on it. The problem comes from the columns with dates in it - they import using the Query in UK format, but after the code has brought the tabs into one, they become US format as text. Can anyone help correct this, as i'm having to use a workaround formula to rearrange the cells at the moment. Sub collate2() Dim firstRow As Double, lastRow As Double, srcRng As Range, destRng As Range, ws As Worksheet firstRow = 2 For Each ws In Sheets 'goes through the workbook grabbing data, if the sheet isn't called "orders" With ws If LCase(.Name) < "analysis" Then 'finds last used row by selecting last row and simulating ctrl-up arrow lastRow = .Cells(Rows.Count, "B").End(xlUp).Row If lastRow = firstRow Then 'makes sure there's some data to copy 'gets range of data in current sheet Set srcRng = .Range(.Cells(firstRow, "A"), .Cells(lastRow, "AL")) 'gets range of first cell on row below data already on sheet Set destRng = Sheets("MasterSheet").Cells(Rows.Count, "B").End(xlUp).Resize(lastRow - firstRow + 1, 40).Offset(1, -1) destRng.Value = srcRng.Value End If End If End With Next ws End Sub -- Richard -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
Issues with Time being converted to Date/Time format | Excel Programming | |||
when loading csv file to Excel, cells get converted to date format | Excel Discussion (Misc queries) | |||
How do I create list validation from code without the text being converted into date format? | Excel Programming |