How it is worked out
serial number = days elapsed since 30 December 1899
Why it is done this way
Spreadsheets store dates as numbers and put a format on top. That is why a date cell can be added and subtracted like any number, and why pasting data between programs sometimes produces columns of five-digit numbers where the dates should be: the format has been lost, not the data.
Step by step
- Take 30 December 1899 as the origin, which is day 0 of the 1900 system
- Count the days elapsed up to the date
- That number is what the cell stores
- To go back, add that many days to the origin
What is worth knowing
Excel believes 1900 was a leap year, so its day 60 is a 29 February that never happened. It was not an oversight: it was copied on purpose from Lotus 1-2-3, the dominant program at the time, so that files would stay compatible, and it has stayed ever since. The consequence is that dates before 1 March 1900 come out a day off, and that number 60 matches no real date. The 1904 system, introduced by Excel for Mac, exists precisely to sidestep the problem, and it is why files sometimes open on another machine with every date shifted by four years and a day.