Why 1904 date system

We have two Excel workbooks , one is using Excel date system and other is using Excel date system. To understand about a possible error which can happen while copying date values between Workbooks using Excel date system and Excel date system , let us enter a sample date December 5, of a Cell in a Workbook which is using date system.

Now, copy and paste the date from Workbook which is using Excel date system to another Workbook which is using Excel date system. Visit the following lessons, if you want to learn more about different date systems in Excel and how to change date systems in Excel.

Once the date value is pasted in the Cell of another Workbook , which is using a different date system date system, in this case , you can immediately notice that the date value has changed. We had already learned that there is a difference of days four years and one day between Excel date system and Excel date system. Click the following link to learn about difference between Excel date systems.

This mistake happens because, Excel stores the date value internally as a date serial number and this date serial number is not changed when you copy and paste it between Workbooks using Excel date system and Excel date system. But, there is a difference of days between two Excel date systems. Note below points when you correct date value copy mistake between and date systems. Excel for Mac and earlier Excel for Mac versions calculate dates based on the date system.

Excel for Mac and Excel for Mac use the date system, which guarantees date compatibility with Excel for Windows. If this option is disabled, you will receive a message asking whether the dates should be converted when pasted. You have two options. You can convert the dates to use the date system recommended. This option makes the dates compatible with other dates in the workbook. Or, you can keep the date system for the pasted dates only. Note: Dates that are copied as part of a chart cannot be converted from the date system and will vary by approximately four years.

You must convert these dates manually. In the date system, dates are calculated by using January 1, , as a starting point. When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, For example, if you enter July 5, , Excel converts the date to the serial number If you choose to convert the pasted data, Excel adjusts the underlying values, and the pasted dates match the dates that you copied.

This is the default date system in earlier versions of Excel for Mac. If you choose not to convert the data and keep the date system, the pasted dates vary from the dates that you copied. Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, , can have two different serial numbers, as follows:. One of the subjects we cover on our courses is the importance of entering dates in an acceptable form when adding them into a cell.

Otherwise you will be unable to use them in a calculation or sort. If you work on a PC and windows the default numbering system starts on 1 st January — that is day 1 and so forth. On a Macintosh computer it all starts 2 nd Jan This is the default system.

It seems Macintosh decided this was a better system. This design was intended to prevent problems related to the fact that was not a leap year.