DateValue gets wrong date number

[StuartBisset]

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

 

[Patrick Molloy]

what is actually in the CSV file? open it in Notepad as Excel cleverly tries
to interpret the data

 

[StuartBisset]

What is in notepad is 01/04/2009

(ie the correct date written in a date format)

 

[Bernie Deitrick]

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

 

[Patrick Molloy]

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.

 

[Ron Rosenfeld]

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

 

[StuartBisset]

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

 

[Patrick Molloy]

MS is an American company?



no offence guys!!!

 

[StuartBisset]

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

 

[Bernie Deitrick]

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]

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.

 

[StuartBisset]

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

 

[Bernie Deitrick]

Stuart,

Try changing:

myVals = Split(wkbCSV.Cells(xLoop, 1), "/")

to

myVals = Split(wkbCSV.Cells(xLoop, 1).Text, "/")

HTH,
Bernie
MS Excel MVP

 

[Patrick Molloy]

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]

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

 

[StuartBisset]

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.

 

[Bernie Deitrick]

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