Excel dating system
Since the worksheet in question would be used entirely by Windows users, I decided to change to the 1900 date system to avoid any problems.Little did I know that by changing that simple date system setting would change the dates by 1462 days.On the Windows-based versions of Excel, dates are determined by the number of days that have elapsed since January 1, 1900.Thus, if we choose a date, such as July 4, 2026, then our nation's sestercentennial will fall 46,207 days after January 1, 1900.
Or, maybe you want to auto fill weekdays or input random dates in your worksheet?
Simply put, each date system calculates dates by counting the number of days from a start date: either January 1, 1900, or January 1, 1904.
According to a Microsoft Support article, early Macs used 1904 to avoid problems with the fact that 1900 was not a leap year. So long as you can expect the start date to be consistent within your spreadsheet then your calculations for future dates will always be correct.
Effective with Office 2011, Excel’s preferences will now default to using the 1900 date system instead of the 1904 date system.
From Excel 2008 to 2011, this change is as simple as unchecking one preference selection, however, all new workbooks will now use the different date system by default. Excel for Mac uses the 1904 date system and Excel for Windows uses the 1900 date system.In this article I'll explain why sometimes Excel dates may mysteriously change by 4 years, and to be more specific, 4 years and 1 day.