VBA 'Now' Provides incorect Date for Today!

[R_S_6]

Hi All,

Firstly I'm running Vista, XL2003 in the UK (Below also tried on XP machine). Date is correct on both machines!

This morning I have discovered an odd quirk in excel (2003). I ran a piece of code that pulls in the 'NOW' date using:

Please Login or Register  to view this content.

Oddly it populates a cell with the date 11/01/2010. I thought that this was funny and i wondered if I had the cell formatted in the USA format. i.e. mm/dd/yyyy. This was not the case and in fact when the cell was formatted to show 14/March/2001 style format it said it was the 11th of January 2010?????

To check this further I ran this code:

Please Login or Register  to view this content.

See attached Spreadsheet for the outcome. I understand after a bit of research that I can use Date in stead as I am just using the Date from the NOW function. My question is why is this happening.....?????

Any Ideas Anyone...
Cheers,
Mark

[royUK]

Your code seems to work for me. is your system datae & time correct?

[Andy Pope]

The Now and Date functions return the correct value.

But you store the result as a string. Excel then converts the string date layout back to a date value. Only it can not tell the difference between dd/mm and mm/dd in your strings.

If you were to have used dd/mmm/yyyy date formatting then the confusion would not have arisen.
Or if you had written the values of Now or Date into variables and then formatted them only for display.

[royUK]

You haven't explicitly declared your variables as a date, so Excel thinks DateVariableUK is a String

Try this

Please Login or Register  to view this content.

[R_S_6]

Hi Roy, thanks for trying this.

The code runs fine - but do you not see the difference between the dates output in the MsgBox to the dates insert in Cells B2 & B3 by macro 'Now_Date()'??

Cells C2 & C3 should display the same date - Mine displays 11Jan2010 in C2 7 01Nov2010 in C3 - They should be the same dates!
Cheers,
Mark

[R_S_6]

Okay i see some extra replies now - I'll declare the variable as a date!

In my original code I didnt even use a variable I had:

Please Login or Register  to view this content.

In the above case there is no variable so how would I sort that?
[In explanation I used the variable to create a test file and for trying to debug the issue]

Cheers,
Mark

[romperstomper]

Simply:

Please Login or Register  to view this content.

[R_S_6]

Roy, Andy, RomperStomper - Thanks all for your help! Very informative as always!

Cheers,
Mark

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[R_S_6]

Already Done ;-)

[royUK]

Thanks, you must have done it as I was posting