Date difference on Mac vs Windows

Redeyebase

New Member
Joined
Dec 21, 2005
Messages
2
I have a spreadsheet with a date column formatted to 3/14/01 style. I created this spreadsheet on a Mac. When I (and one other person) opened the same file on a Windows computer, copied and pasted the value in one of the date cells, it pastes the value with a different date.

In other words, we copied 3/11/2007 and pasted it into a totally different workbook and it displayed as 3/11/2003.

Why? And how can we prevent Excel from doing this (if possible)?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Excel for Windows uses the 1900 date system by default, Macintosh uses the 1904 date system by default. If you are using both, switch the options on one of them so they match.

From the help file:

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.


The 1900 and 1904 date systems

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. The default date system for Microsoft Excel for the Macintosh is 1904. You can change the date system. On the Tools menu, click Options, click the Calculation tab, and then select or clear the 1904 date system check box.

The date system is changed automatically when you open a document from another platform. For example, if you are working in Excel for Windows and you open a document created in Excel for the Macintosh, the 1904 date system check box is selected automatically.
The following table shows the first date and the last date for each date system and the serial value associated with each date.

Date system First date Last date
1900 January 1, 1900
(serial value 1) December 31, 9999
(serial value 2958465)
1904 January 2, 1904
(serial value 1) December 31, 9999
(serial value 2957003)
 

Redeyebase

New Member
Joined
Dec 21, 2005
Messages
2
That worked, thanks. However, we now found another funny glitch.

We changed the date format on the Windows computer to use 1904 date system. Fine. But when we copied and pasted a date value to a new workbook, Excel did not keep this Calculation option change. It reverted back to the 1900 date system (unchecked in Options/Calculation).

Why would it do this?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry, I can't find a way to default it for new workbooks, it seems it is set on a workbook by workbook basis. Keep in mind that if dates are already in a workbook and you change the date system, existing dates will be changed to reflect the new date system.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top