Why do dates change when I copy/paste into another sheet?

[maloden]

I receive a spreadsheet from someone and then copy/paste as values into my
spreadsheet. When I do that the dates change and I've tried formatting the
original sheet, checking input for issues (extra space in front is one
sometimes), but can't seem to get it to copy cleanly. It seems to add a day
and 4 years. Then I take my spreadsheet and put it into a new book and the
dates change just making a copy of the sheet, but this time it deducts a day
and 4 years. Other people in my copy who have done this report have had
similar or same issues with the date column. We just upgraded to 2007
version but was a problem prior to that.

 

[Pete_UK]

Excel has two date systems - one based on a reference date of 1st Jan
1900 and the other based on 1904 (commonly used on Macs). If you copy
a date from one system to another you will have about 4 years
difference. To avoid it, ensure that both systems are using the same
date format. In earlier versions of Excel you would do that through
Tools | Options | Calculation tab and you will find 1904 date system
at the bottom of the panel, but I'm not sure how you do this in Excel
2007.

Hope this helps.

Pete