Dates change when cut and pasted

**calif2ariz** · 12-19-2010, 12:31 AM

Hello - and help please.
When I copy a date such as 12/20/10 from one spreadsheet and paste it to another the date changes to, for example, 12/20/14.
Thanks

**MarvinP** · 12-19-2010, 12:37 AM

Hi calif2ariz,

If you simply type in the dates are they the same between workbooks? Have you set the format of the cells the same between the two? Is there a formula working on the cells of either sheet? Are both workbooks .xls and created with the same version of Excel? If you copy and paste the date into Notepad or Word does it change or is it only with the destination worksheet?

I'd be looking at the above to discover what is going on.

hth.

**calif2ariz** · 12-19-2010, 12:55 AM

Hi hth,
thanks for the response.
If I type the dates, they are as I type them. Problem is only cut or copy and paste.
Yes, format is the same.
No formula for either cells - destination is a blank sheet.
both workbooks .xls and same version.
If I copy and paste into word it is just fine, no change.

**MarvinP** · 12-19-2010, 01:31 AM

From you answers there must be a difference in the versions of Excel from one sheet to the next. Is one of the sheets very old and using date formats from Excel pre 95?

also hth = hope that helps - I'm MarvinP

Is there a chance a macro is working on the second sheet to add two years?
Also look at your system settings to see if your date is set correctly. Also look at your language settings and timezone.

I'm clueless on this.

**MarvinP** · 12-19-2010, 02:01 AM

Hi,

After some research (I asked my wife), you need to look at the file->options->advanced and uncheck the "Use 1904 date system".

Good question - let us know if that is the answer.

**calif2ariz** · 12-19-2010, 12:53 PM

Hi Marvin,
Thanks for all your thoughts - and thanks for the hth info

First let me say I made a mistake in that my excel is not 2003, I'm on mac 2008 (this mac adventure has been quite the trial)... anyhow. I took your ideas of trying to paste it other places and have discovered that if I open a new workbook and enter date and then open another new workbook it cut and pastes just fine. SO, there must be a formula as you said but I can not figure out what it could be, cannot see one and don't know how to fix but at least I know :-).
You have solved my mystery. Thanks!

**martindwilson** · 12-19-2010, 03:08 PM

definately something to do with the 1904 date system mac uses as default
from microsoft

How to Transfer Files Between Excel for the Macintosh and Excel for Windows
By default, Excel for the Macintosh uses the 1904 date system, and Excel for Windows uses the 1900 date system. This means that when you type the serial number 1 in Excel for the Macintosh and format it as a date, Excel displays it as 1/2/1904 12:00 a.m. Excel for Windows displays the serial number 1 as 1/1/1900 12:00 a.m. If you transfer files from Excel for the Macintosh to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date.

In Microsoft Excel for Mac, you can change to the 1900 date system by clicking Preferences (on the Edit menu in Excel 2001 for Mac, or on the Excel menu in later versions), clicking the Calculation tab, and then clicking to clear the 1904 date system check box. In Excel for Windows, you can change to the 1904 date system by clicking Options on the Tools menu, clicking the Calculation tab, and then clicking to select the 1904 date system check box.