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

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


"StuartBisset" <(E-Mail Removed)> wrote in message
news:f32503eb-6eb6-46b4-9792-(E-Mail Removed)...
> 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

What is in notepad is 01/04/2009

(ie the correct date written in a date format)

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" <(E-Mail Removed)> wrote in message
news:f32503eb-6eb6-46b4-9792-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:36d2912a-db4b-4259-871a-(E-Mail Removed)...
>
> What is in notepad is 01/04/2009
>
> (ie the correct date written in a date format)

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

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

MS is an American company?



no offence guys!!!

"StuartBisset" <(E-Mail Removed)> wrote in message
news:fb2169af-da5b-4968-bab8-(E-Mail Removed)...
> 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

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

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" <(E-Mail Removed)> wrote in message
news:8edbd7bf-de9d-4968-a175-(E-Mail Removed)...
> 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