How Microsoft Excel stores dates and times

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. 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.

A day equals 1, half a day is .5 and an hour time interval is therefore 1/24=0.041666667

Dates entered without a specified year are assumed to be in the current year

Dates entered with a 2 digit year are stored as pre-2000 dates if the year is between 30 and 99 or as post-2000 dates if the year is between 0 and 29.

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900 and Microsoft Excel for the Macintosh uses 1904 date system.

Two different date systems got created as 1900 is not a leap year and design of early Macintosh computers did not support dates before Jan 1, 1904. 1900, although divisible by 4, wasn’t a leap year. By using 1904 as a base, they saved a few bytes code, and every byte counted when they tried to squeeze the entire MacOS into 64KiB of ROM…

The following table shows the first date and the last date for each date system and the serial value associated with each date.



To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box. This setting is workbook specific, you can have two workbooks on the same system with 1900 and 1904 date system.

Be careful while changing the date system. The workbooks with actual dates recorded will also shift by 1462 days.

Similarly you may get into trouble when linking two workbooks set with two different date systems. in case you get struck with such situation you can correct this by adding or subtracting 1462 from one of the workbook as the case may be.

Suppose Book1 works on 1900 date system and book2 works on 1904 date system. If you are linking dates from 1904 to 1900 date system workbook, you will add 1462 to all the dates linked from 1904 date system. In a reverse scenario you will subtract 1462.

Dates in Excel , Excel Dates, Excel Date