Excel/SQLite date serial numbers

sqlite> SELECT DATE('1899-12-30', '+40074 days');
2009-09-18

This seems to work:

sqlite> SELECT DATETIME((49400 * 3600 * 24) - 3014928000, 'unixepoch');
2009-09-18 00:00:00

Honestly, I just guess and checked on the constant there, but I'm sure there's simple math to back it up.

It looks like it works for dates earlier than the epoch as well, but I haven't tested it thoroughly.

To handle excel date value and return human-readable date in sqlite3 consider:

-- unix epoch is 00:00:00 UTC on 1 January 1970 (1970/01/01)

-- 86400 = Seconds in a day

-- 25569 = Days between 1970/01/01 and 1900/01/01 (min date in Windows Excel)

The formula, or statement rather:

SELECT DATETIME(( EXCEL_DATE - 25569) * 86400 , 'unixepoch') AS UNIX_DATE;

Example completed from excel date provided in the initial question:

sqlite> SELECT DATETIME(( 40074 - 25569) * 86400 , 'unixepoch') AS UNIX_DATE;

2009-09-18 00:00:00