"Unix timestamp to Excel date: why serial 60 is a day that never existed"

When you paste a Unix timestamp like 1756771200 into a spreadsheet or export a sheet with numbers like 46236.5 instead of dates, both processes can go wrong in three places: the epoch, the unit, and a fake day in February 1900. A Unix timestamp is the number of seconds since 1 January 1970, 00:00 UTC, with leap seconds ignored. If you see 13 digits, it's in milliseconds; 16, 19 digits are microseconds and nanoseconds respectively.

Excel's serial date is the number of days since its own epoch, with a decimal fraction representing the time of day. Excel does not store time zones, so the number represents whatever you input. To convert Unix seconds to Excel serial, divide by 86400 and add 25569, the serial number for 1 January 1970. To convert Excel serial to Unix seconds, subtract 25569 and multiply by 86400.

Both formulas produce UTC time. Excel's default 1900 date system considers serial 1 as 1 January 1900 and serial 60 as 29 February 1900, but February 1900 does not exist as it was not a leap year. This bug was unintentionally included in Excel in 1987 to maintain compatibility with Lotus 1-2-3 spreadsheets. The result is that every serial number below 61 is one day off against the real calendar.

Excel does not handle dates before 1 March 1900 correctly either, which can lead to errors in formulas like WEEKDAY. Most modern spreadsheet software, like Google Sheets and LibreOffice, do not include the phantom day. If you're working with older Mac versions of Excel, be aware that they used a 1904 date system, making dates appear 1,462 days off when opened in other versions.

To avoid confusion when working with timestamps, always double-check the number of digits and the unit before converting, and remember that Excel serial 2,958,465 represents the last possible date (31 December 9999).