Problem with copying dates between files

[kmp28385]

I'm trying to copy a column of dates in one file to another file in Excel
2003. When I copy the column to the new file the dates are changed. For
example, the original cell in file A reads "06/09/97" for June 9, 1997. When
I copy that cell to file B the copied cell reads "06/08/93". Why is Excel
changing the dates and how can I fix it? Any advice would be greatly
appreciated.

 

[T. Valko]

It sounds like the file you are copying from is using the 1904 date system
and the file you are pasting to is using the default 1900 date system.

You have to add 1462 days to the pasted dates. One way to do that:

Enter 1462 in an empty cell
Copy that cell: Edit>Copy
Select the date cells that need changed
Then, Edit>Paste Special>Add>OK

This will change the cell format so you'll have to reformat as DATE.

 

[kmp28385]

It worked! Thanks so much. I never would have figured that one out on my
own. What an obscure Excel quirk. Thanks again!

 

[T. Valko]

You're welcome. Thanks for the feedback!

You can change the date system in the source file but you'll have the same
problem in the source file, all of your dates will be off by 1462 days.

Tools>Options>Calculation tab>1904 date system