Date to Excel serial number

Excel stores a date as a number of days and shows you a date through formatting, which is why changing a cell’s format turns 45292 into 1 January 2024 and back. The fractional part is the time: 0.5 is midday, 0.75 is six in the evening. That is also why subtracting two dates gives a number of days directly, and why multiplying a time difference by 24 gives hours.

The famous bug is real and deliberate. Excel treats 1900 as a leap year, which it was not — the rule skips century years unless they divide by 400. Lotus 1-2-3 had the bug in 1983, and Microsoft copied it for file compatibility rather than break every existing spreadsheet. The consequence is that serial 60 is 29 February 1900, a day that never existed, and every date before 1 March 1900 is off by one. Dates after that are correct, which is why almost nobody notices.

The Mac version of Excel historically used a different epoch — 1904 rather than 1900 — and files created under it can still be encountered. A spreadsheet whose dates are all four years and one day out has come from that system, and the workbook has a setting to switch.

When importing, the trap is that a CSV has no formatting, so a column of serials arrives as plain numbers. Excel guesses, and it guesses inconsistently — which is why 01/02/2024 is a famous source of ruined data and why serial numbers, ironically, are the safer thing to exchange.