thoughts on tech targeted at helping small businesses innovate

How to Stop Dates Changing when Cutting and Pasting between Excel Files

Posted: October 20th, 2010 | Author: | Filed under: Help | Tags: , , , , | 1 Comment »

So I was moving some old financial records across to a single new excel file in Microsoft Excel 2008 when I noticed that the dates were changing by about four years, perplexed I resorted to a great deal of googling before I came across the reason for this.

In Microsoft Excel 2004 for Mac all Excel files by default used the Microsoft system base date of 01 January 1900 but in Microsoft Excel 2008 for Mac (and onwards) they have changed this to use the Mac system base date of 02 January 1904. Why I’m not sure; standardisation perhaps?

What Microsoft have not done is made this very well known. So if you’re opening an .xls file created in Office for Mac prior to the 2008 version it’ll be using the 1900 base date and if you created an .xls file after this date it’ll be using the the 1904 system. So you’ll have to change it before cutting.

Do in preferences select this setting:

Calculation Preferences Excel for Mac 2008

And then tick this box to use the base 1900 date system:

1904 Base System Date

Unfortunately I am yet to discover a way to change this permanently. :-(


  • http://twitoaster.com/country-gb/nealandco/ nealandco

    [New Post] How to Stop Dates Changing when Cutting and Pasting between Excel Files – via #twitoaster http://blog.nealandassociates.co.uk/2010