How to use the excel date serial number converter
- Choose Serial to date or Date to serial, and the date system: 1900 for most files, 1904 for some older Mac workbooks.
- For a serial, type the number. The part after the point is the time of day. For a date, pick it and, if you like, add a time such as 13:30.
- Read the date, weekday and time, or the serial number, with the steps.
- Copy or print the result.
Formula
1900 system: serial 1 = January 1, 1900. From serial 61, date = December 30, 1899 + serial days
1904 system: serial 0 = January 1, 1904. date = January 1, 1904 + serial days
Time of day = fraction of the serial x 86,400 seconds
Serial 60 is February 29, 1900 in the 1900 system, a day that never existed. Dates and times are shown to the second.
Serial 45000
In the 1900 system, serial 45000 is 45,000 days after December 30, 1899, which is Wednesday, March 15, 2023. In the 1904 system the same number means 45,000 days after January 1, 1904, which is March 16, 2027, so the system setting matters.
Serial 45000.5 adds half a day, so it is noon on the same date. Going the other way, March 15, 2023 at midnight is 45000, and with the time 12:00 it is 45000.5.
The 1900 leap year bug
Excel's 1900 system treats 1900 as a leap year, so it counts a February 29, 1900 that did not exist. This was kept for compatibility with an older spreadsheet program. The result is that serials 1 to 59 are January 1 to February 28, 1900, serial 60 is the false February 29, and from serial 61 the dates are March 1, 1900 onward, matching the real calendar.
For that reason this tool shows serial 60 as February 29, 1900 with a warning, and refuses to accept that date when you convert from a date. Serial 0 is shown as Excel's day zero. Excel's own WEEKDAY function is off by one day for serials below 61 for the same reason, while the weekday shown here is the real one.
Limits and the 1904 system
The largest date Excel accepts is December 31, 9999, which is serial 2,958,465 in the 1900 system and 2,957,003 in the 1904 system. Negative serials are not used, and dates before January 1, 1900 (or 1904) have no serial in the chosen system, so they are rejected with a message.
The 1904 system exists in some workbooks that began on older Mac versions. Copying data between workbooks that use different systems shifts dates by 1,462 days, so check which one your file uses. This tool is arithmetic on dates and does not read any file. For other date math try the date calculator, the days between dates calculator and the day of the week calculator.
Assumptions and limits
- The 1900 system includes the false February 29, 1900 and the 1904 system does not.
- Times are shown to the nearest second.
- Serials are limited to 0 through 2,958,465 (1900) or 2,957,003 (1904).
- Dates are plain calendar dates with no time zone or daylight saving time.
Frequently asked questions
How do I convert an Excel serial number to a date?
Enter the number and choose the 1900 system. Serial 45000, for example, is March 15, 2023. In Excel you can also format the cell as a date.
Why is there a February 29, 1900?
Excel keeps it so old files that counted 1900 as a leap year still line up. The day never existed, but serial 60 is reserved for it.
What is the 1904 date system?
It is an option in which day 0 is January 1, 1904. Some workbooks that began on older Mac versions use it, and dates differ by 1,462 days from the 1900 system.
What does the decimal part of a serial mean?
It is the time of day as a fraction of 24 hours. 0.5 is noon and 0.25 is 6:00 in the morning.