For a list of BASHing data 2 blog posts see the index page. 
Convert Microsoft serial day numbers to YYYY-MM-DD
The following function will convert most Microsoft serial day numbers like "44883" to an ISO 8601 date:
msdate() { ((wanted = "$1"-2)); date -d "1900-01-01 + $wanted days" +"%Y-%m-%d"; }
Why "most" dates? And why subtract two days from Microsoft's day number?
One day is subtracted because in the "1900 date system" adopted by Microsoft, the start date is 1 January 1900, meaning that 1 January 1900 is day 1. Day 2 is 2 January 1900, day 3 is 3 January 1900, and so on. For the date command in the function to calculate the date "1900-01-03" from the day number "3", it adds two days, or (Microsoft day number) - 1, to 1 January 1900.
The second day is subtracted because there's a quirk in the Microsoft system of day numbering. According to Microsoft, day 60 is 29 February 1900, but that date didn't exist: 1900 wasn't a leap year. On the real-world calendar, it was 1 March 1900 that was 60 days after 1 January 1900. This Microsoft quirk adds a non-existent day to their day-numbering system.
The extra day explains why I calculate wanted by subtracting a second day from the day number for "most" dates, meaning dates after 28 February 1900.
Microsoft explains that the 29 February 1900 fiction appeared in Lotus 1-2-3 last century and was adopted in the first Excel version for compatibility with Lotus, almost 40 years ago.
Non-Excel spreadsheets behave variously when faced with this day-numbering problem. In the graphic below I show what happens with LibreOffice Calc, Gnumeric and Google Sheets when I format the numbers in the left-hand column as YYYY-MM-DD dates. Calc and Sheets count 31 December 1899 as day 1 and ignore 29 February 1900. Gnumeric counts days from 1 January 1900 and numbers 29 February 1900, but shows that date as character gibberish ("#####...."). All three spreadsheet programs have Excel day-number compatibility beginning with 1 March 1900.
From this Microsoft help page:
Last update: 2024-02-23
The blog posts on this website are licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License