Just curious about Excel copying dates between workbooks???

[Steve]

Excel 2K, Windows XP. I know Excel stores dates as numbers.

I use code to copy data from a .csv sheet. The data normally include a
column of text in dd/mm/yyy format. Normally, I convert this to numeric/date
format after it is copied in.

While developing, I am not refreshing the .csv file. Instead, I decided to
open it and age the data by a few days. To age it I converted the .csv to a
date then added (say) five days. The column of dates were formatted
"dd/mm/yyyy" to ensure they 'aged' correctly. Result - all looks as it
should in the .csv sheet.

Back to the original sheet. Click the button to run the copy data in macro.
Data copies. However, all dates were 'confused' by the process. For example,
instead of seeing 08/11/2005, the date is copied in as 11/08/2005 (ie
approximately 3 months incorrect).

This is not causing me a problem. The workaround is to open the .csv sheet
and format column A as a number, the correct value now copies in. However, I
am curious why the values are incorrect when Excel is working with numbers
in dd/mm/yyyy format between workbooks.

Steve

 

[Tom Ogilvy]

in most cases, vba interprets date like strings as if they were in US
sequence. If you work with date serial numbers rather than strings, you
will not have a problem.