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

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.

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

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

On May 21, 4:44*pm, maloden wrote:
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.

yes
I just tried playing with this in Excel 2008.

Go to excel: preferences: calculations and there will be a checkbox with 'use the 1904 date system'. If your 2 sheets are different it will chance the dates when u cut and paste.

I got around this by cutting my data to a new spreadsheet and copying it all. then changing the preference to 'use the 1904 date system' to match the 2nd sheet i need to copy the data to. Then you do 'paste special' (u bring up this menu using alt-right click on the mac or in edit menu also).

in here select paste as 'unicode text'. this will give u your data unchanged but with the correct date mode. u can then copy and paste it across to your 2nd excel sheet without problem.