Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Hi Folks
I have written a macro that grabs data from a seperate CSV file and puts it into my workbook. The CSV file is a download from another system and there was an issue with some dates coming out in numeric format and some coming out in text format. I got round this by using ... DateValue(wkbCSV.cells(xLoop,1))*1 (where "wbkCSV" is the CSV file and "xLoop" cycles thru all the rows) .... to put the date integers into my array. However the CSV file shows 01/04/2009 (ie 1st April, integer 39904) but when I spit the array out onto the worksheet it shows as 04/01/2009 (ie 4th Jan, integer 39817). There is not a problem with my regional settings in control panel (set to dd/mm/yyyy) or with the number format in excel (also set to dd/mm/ yyyy). When I select the date cell in the csv file and I go to the immediate window in the VBE and type .... ? DateValue(selection)*1 .... it returns 39904 (correct). But when I run my macro it seems to grab 39817 instead. How can this be? Any help would be much appreciated. Cheers Stuart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
what is actually in the CSV file? open it in Notepad as Excel cleverly tries
to interpret the data "StuartBisset" wrote in message ... Hi Folks I have written a macro that grabs data from a seperate CSV file and puts it into my workbook. The CSV file is a download from another system and there was an issue with some dates coming out in numeric format and some coming out in text format. I got round this by using ... DateValue(wkbCSV.cells(xLoop,1))*1 (where "wbkCSV" is the CSV file and "xLoop" cycles thru all the rows) ... to put the date integers into my array. However the CSV file shows 01/04/2009 (ie 1st April, integer 39904) but when I spit the array out onto the worksheet it shows as 04/01/2009 (ie 4th Jan, integer 39817). There is not a problem with my regional settings in control panel (set to dd/mm/yyyy) or with the number format in excel (also set to dd/mm/ yyyy). When I select the date cell in the csv file and I go to the immediate window in the VBE and type .... ? DateValue(selection)*1 ... it returns 39904 (correct). But when I run my macro it seems to grab 39817 instead. How can this be? Any help would be much appreciated. Cheers Stuart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
What is in notepad is 01/04/2009 (ie the correct date written in a date format) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Stuart,
VBA dates use US default - mm/dd/yyyy, not dd/mm/yyyy - as the first interpretation of the date string. If that fails (the dd value is 13 or higher), then it tries dd/mm/yyyy. So you need to extract your month and day: Dim myVals As Variant Dim myDay As Integer Dim myMonth As Integer Dim myYear As Integer myVals = Split(wkbCSV.Cells(xLoop, 1), "/") myDay = myVals(0) myMonth = myVals(1) myYear = myVals(2) myArr(xLoop) = DateSerial(myYear, myMonth, myDay) Should work... not sure what your array is named... HTH, Bernie MS Excel MVP "StuartBisset" wrote in message ... Hi Folks I have written a macro that grabs data from a seperate CSV file and puts it into my workbook. The CSV file is a download from another system and there was an issue with some dates coming out in numeric format and some coming out in text format. I got round this by using ... DateValue(wkbCSV.cells(xLoop,1))*1 (where "wbkCSV" is the CSV file and "xLoop" cycles thru all the rows) ... to put the date integers into my array. However the CSV file shows 01/04/2009 (ie 1st April, integer 39904) but when I spit the array out onto the worksheet it shows as 04/01/2009 (ie 4th Jan, integer 39817). There is not a problem with my regional settings in control panel (set to dd/mm/yyyy) or with the number format in excel (also set to dd/mm/ yyyy). When I select the date cell in the csv file and I go to the immediate window in the VBE and type .... ? DateValue(selection)*1 ... it returns 39904 (correct). But when I run my macro it seems to grab 39817 instead. How can this be? Any help would be much appreciated. Cheers Stuart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
As Bernie indicates, this would be interpreted by Excel as a US date, ie Jan
4th not 1st Apr another way around this would be to read the data into excel using VBA and then manipulating the text appropriately. "StuartBisset" wrote in message ... What is in notepad is 01/04/2009 (ie the correct date written in a date format) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
On Thu, 14 May 2009 10:27:30 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org wrote: Stuart, VBA dates use US default - mm/dd/yyyy, not dd/mm/yyyy - as the first interpretation of the date string. If that fails (the dd value is 13 or higher), then it tries dd/mm/yyyy. Bernie, I've seen this written a bunch of times and never checked it out. But today I did and, at least on my system (VBA 6.5), Datevalue behaves exactly as documented. According to HELP, "DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. " So I did the following in the Immediate window: ?format(datevalue("01/04/2009"),"dd-mmm-yyyy") With my default US settings, the result was: 04-Jan-2009 However, when I changed the Short Date format in my regional settings to d/M/yyyy, the result was: 01-Apr-2009 I then changed it again to UK settings, and again got the 01-Apr-2009 result. So it seems to me there is something else going on than VBA date handling issues. --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Bernie, Patrick
Many thanks for your help. Your "Split" solution looks like it will work and for that I am grateful - but I must admit I find it extremely odd that DateValue would return the correct value when it was used in the immediate window but a different/incorrect value when used within a macro. Can either of you explain the reason for that? Cheers Stuart |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
MS is an American company?
:) no offence guys!!! "StuartBisset" wrote in message ... Bernie, Patrick Many thanks for your help. Your "Split" solution looks like it will work and for that I am grateful - but I must admit I find it extremely odd that DateValue would return the correct value when it was used in the immediate window but a different/incorrect value when used within a macro. Can either of you explain the reason for that? Cheers Stuart |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Ron
That is interesting. I have just run the suggested SPLIT solution and I'm afraid it didn't work (sorry Bernie!). The macro is somehow swapping round the myDay & myMonth values - i have checked all this using debug.print as well as looking at the end results in the worksheet. Does anyone have any other smart ideas???? Cheers folks Stuart |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Stuart,
You should only run the Split on cells that are text and not values - otherwise, the value is a date - a number - without \s. HTH, Bernie MS Excel MVP "StuartBisset" wrote in message ... Ron That is interesting. I have just run the suggested SPLIT solution and I'm afraid it didn't work (sorry Bernie!). The macro is somehow swapping round the myDay & myMonth values - i have checked all this using debug.print as well as looking at the end results in the worksheet. Does anyone have any other smart ideas???? Cheers folks Stuart |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
The macro is somehow swapping round the myDay & myMonth values .... sorry that wasn't explained very well. What I mean is that part (0) and part (1) of the myVals Split are being swapped round by VBA prior to being posted to the myDay & myMonth variables. It seems like when I do this: myVals = Split(wkbCSV.Cells(xLoop, 1), "/") the split function recognises the expression as a date and swaps round month and day prior to splitting it. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
You should only run the Split on cells that are text and not values - otherwise, the value is a date - a number - without \s. Bernie Apologies but I may have accidentally misled earlier. When I open the CSV in notepad all the dates seem to be in the same format - dd/mm/ yyyy. When I grabbed the data from the CSV and put it into my new workbook via the macro it appeared as though some were text and some were numeric as I could see that some were formatting to the left (like text would) and some were formatting to the right (like numeric would). It is now clear that this was because the variant array was treating the dates with days <= 12 as numbers and the dates with days 12 as text. Any further ideas as to how I might fix this guddle? Many Thanks Stuart |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Stuart,
Try changing: myVals = Split(wkbCSV.Cells(xLoop, 1), "/") to myVals = Split(wkbCSV.Cells(xLoop, 1).Text, "/") HTH, Bernie MS Excel MVP "StuartBisset" wrote in message ... You should only run the Split on cells that are text and not values - otherwise, the value is a date - a number - without \s. Bernie Apologies but I may have accidentally misled earlier. When I open the CSV in notepad all the dates seem to be in the same format - dd/mm/ yyyy. When I grabbed the data from the CSV and put it into my new workbook via the macro it appeared as though some were text and some were numeric as I could see that some were formatting to the left (like text would) and some were formatting to the right (like numeric would). It is now clear that this was because the variant array was treating the dates with days <= 12 as numbers and the dates with days 12 as text. Any further ideas as to how I might fix this guddle? Many Thanks Stuart |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
what's happening is that where the CSV contains a "date" which is USA style,
then Excel is interpreting as a date and loads it as a date. Where the text isn't an American date, such as 17/12/2008, then this is loaded as text. you can see this if you format the column or cells involved to 'general'. Those that are 'dates' will be seen as 5 digit numbers, while unconverted dates are seen as text. "StuartBisset" wrote in message ... You should only run the Split on cells that are text and not values - otherwise, the value is a date - a number - without \s. Bernie Apologies but I may have accidentally misled earlier. When I open the CSV in notepad all the dates seem to be in the same format - dd/mm/ yyyy. When I grabbed the data from the CSV and put it into my new workbook via the macro it appeared as though some were text and some were numeric as I could see that some were formatting to the left (like text would) and some were formatting to the right (like numeric would). It is now clear that this was because the variant array was treating the dates with days <= 12 as numbers and the dates with days 12 as text. Any further ideas as to how I might fix this guddle? Many Thanks Stuart |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Try changing: myVals = Split(wkbCSV.Cells(xLoop, 1), "/") to myVals = Split(wkbCSV.Cells(xLoop, 1).Text, "/") Bernie Thanks but no joy. I have also tried using Left, Mid & Right but excel is swapping day & month around (when it can ie on US dates) before I can grab them! Here's hoping you have a few other tricks up your sleeve. I will keep trying other things at this end. Thanks for your help today. I'm in Scotland so its bed time for me now. :-) Stuart |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
Try changing: myVals = Split(wkbCSV.Cells(xLoop, 1), "/") to myVals = Split(wkbCSV.Cells(xLoop, 1).Text, "/") Bernie Thanks but no joy. Bernie, I should have said the wkbCSV.Cells(xLoop, 1).Text returns "########" as a value so Split() can't split it. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateValue gets wrong date number
One possible solution is to change your import, so that column is imported
as strings rather than being allowed to be interpreted as dates when the file is opened. Bernie "StuartBisset" wrote in message ... Try changing: myVals = Split(wkbCSV.Cells(xLoop, 1), "/") to myVals = Split(wkbCSV.Cells(xLoop, 1).Text, "/") Bernie Thanks but no joy. Bernie, I should have said the wkbCSV.Cells(xLoop, 1).Text returns "########" as a value so Split() can't split it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATEVALUE, etc. and Australian date format | Excel Worksheet Functions | |||
conditional formatting on date where today is or = datevalue | Excel Discussion (Misc queries) | |||
Date Conversion- DATEVALUE- including minutes | Excel Worksheet Functions | |||
Date-related problems - max and datevalue | Excel Discussion (Misc queries) | |||
DATEVALUE OF CURRENT DATE | Excel Worksheet Functions |