Here is the mapping for the link, assuming UTC for spreadsheet systems such as Microsoft Excel:
Unix Excel Mac Excel Human Date Human Time Excel Epoch -2209075200 -1462 0 1900/01/00* 00:00:00 (local) Excel ≤ 2011 Mac† -2082758400 0 1462 1904/12/31 00:00:00 (local) Unix Epoch 0 24107 25569 1970/01/01 00:00:00 UTC Example Below 1234567890 38395.6 39857.6 2009/02/13 23:31:30 UTC Signed Int Max 2147483648 51886 50424 2038/01/19 03:14:08 UTC One Second 1 0.0000115740… — 00:00:01 One Hour 3600 0.0416666666… ― 01:00:00 One Day 86400 1 1 ― 24:00:00
<sub> * "Jan Zero, 1900" - 1899/12/31; see the Error section below. † Excel 2011 for Mac (and later) uses the 1904 date system .
As I often use awk to handle CSV and space-delimited content, I developed a way to convert the UNIX era to a timezone / DST local Excel date format:
echo 1234567890 |awk '{ # tries GNU date, tries BSD date on failure cmd = sprintf("date -d@%d +%%z 2>/dev/null || date -jf %%s %d +%%z", $1, $1) cmd |getline tz # read in time-specific offset hours = substr(tz, 2, 2) + substr(tz, 4) / 60 # hours + minutes (hi, India) if (tz ~ /^-/) hours *= -1 # offset direction (east/west) excel = $1/86400 + hours/24 + 25569 # as days, plus offset printf "%.9f\n", excel }'
I used echo for this example, but you can pass the file where the first column (for the first cell in .csv format, called as awk -F, ) is the UNIX era. Alter $1 to indicate the desired column / cell number or use a variable instead.
This makes the date system call. If you will have a reliable version of GNU, you can remove 2>/dev/null || date … +%%z 2>/dev/null || date … +%%z and the second , $1 . Given how common GNU is, I would not recommend the BSD version.
getline reads the timezone offset displayed by date +%z in tz , which is then converted to hours . The format will look like -0700 ( PDT ) or +0530 ( IST ), so the first substring received is 07 or 05 , the second is 00 or 30 (then divided by 60, expressed in hours), and the third use of tz shows whether our offset negative and changes the hours if necessary.
The formula given in all the other answers on this page is used to set excel with the addition of the time zone setting for daylight as hours/24 .
If you are using an older version of Excel for Mac, you need to use 24107 instead of 25569 (see the comparison above).
To convert any arbitrary time without time to a time convenient for Excel with a GNU date:
echo "last thursday" |awk '{ cmd = sprintf("date -d \"%s\" +\"%%s %%z\"", $0) cmd |getline hours = substr($2, 2, 2) + substr($2, 4) / 60 if ($2 ~ /^-/) hours *= -1 excel = $1/86400 + hours/24 + 25569 printf "%.9f\n", excel }'
This is basically the same code, but date -d no longer has @ to represent unix epoch (considering how capable the parser is, I'm really surprised that @ is mandatory; what other date format has 9-10 digits?), And now he has requested two exits: era and time zone offset. Therefore, you can use, for example, @1234567890 as an input.
Mistake
Lotus 1-2-3 (the source software for the spreadsheet) intentionally treated 1900 as a leap year , despite the fact that it was not (this reduced the code base at the time when each byte was counted). Microsoft Excel saved this error for compatibility by skipping day 60 (dummy 1900/02/29) while maintaining the Lotus 1-2-3 display day 59-1900 / 02/28. LibreOffice instead set a day from 60 to 1900/02/28 and clicked all the previous days ago.
Any date before 1900/03/01 can be a day off:
Day Excel LibreOffice -1 — 1899/12/29 0 1900/01/00* 1899/12/30 1 1900/01/01 1899/12/31 2 1900/01/02 1900/01/01 … 59 1900/02/28 1900/02/27 60 1900/02/29(!) 1900/02/28 61 1900/03/01 1900/03/01
Excel does not recognize negative dates and has a special definition of Zeroth of January for day zero. Internally, Excel does handle negative dates (they are just numbers after all), but it does not know how to display them as dates (nor can it convert older dates to negative numbers). February 29, 1900, which never happened, is recognized by Excel, but not LibreOffice.