Excel date to Unix timestamp
Non of these worked for me... when I converted the timestamp back it's 4 years off.
This worked perfectly: =(A2-DATE(1970,1,1))*86400
Credit goes to: Filip Czaja http://fczaja.blogspot.ca
Original Post: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html
Windows and Mac Excel (2011):
Unix Timestamp = (Excel Timestamp - 25569) * 86400
Excel Timestamp = (Unix Timestamp / 86400) + 25569
MAC OS X (2007):
Unix Timestamp = (Excel Timestamp - 24107) * 86400
Excel Timestamp = (Unix Timestamp / 86400) + 24107
For Reference:
86400 = Seconds in a day
25569 = Days between 1970/01/01 and 1900/01/01 (min date in Windows Excel)
24107 = Days between 1970/01/01 and 1904/01/02 (min date in Mac Excel 2007)
If we assume the date in Excel is in A1 cell formatted as Date and the Unix timestamp should be in a A2 cell formatted as number the formula in A2 should be:
= (A1 * 86400) - 2209075200
where:
86400 is the number of seconds in the day 2209075200 is the number of seconds between 1900-01-01 and 1970-01-01 which are the base dates for Excel and Unix timestamps.
The above is true for Windows. On Mac the base date in Excel is 1904-01-01 and the seconds number should be corrected to: 2082844800
Here is a mapping for reference, assuming UTC for spreadsheet systems like 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
* “Jan Zero, 1900” is 1899/12/31; see the Bug section below. † Excel 2011 for Mac (and older) use the 1904 date system.
As I often use awk to process CSV and space-delimited content, I developed a way to convert UNIX epoch to timezone/DST-appropriate 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 pipe a file where the first column (for the first cell in .csv format, call it as awk -F,) is a UNIX epoch. Alter $1 to represent your desired column/cell number or use a variable instead.
This makes a system call to date. If you will reliably have the GNU version, you can remove the 2>/dev/null || date … +%%z and the second , $1. Given how common GNU is, I wouldn't recommend assuming BSD's version.
The getline reads the time zone offset outputted by date +%z into tz, which is then translated into hours. The format will be like -0700 (PDT) or +0530 (IST), so the first substring extracted is 07 or 05, the second is 00 or 30 (then divided by 60 to be expressed in hours), and the third use of tz sees whether our offset is negative and alters hours if needed.
The formula given in all of the other answers on this page is used to set excel, with the addition of the daylight-savings-aware time zone adjustment as hours/24.
If you're on an older version of Excel for Mac, you'll need to use 24107 in place of 25569 (see the mapping above).
To convert any arbitrary non-epoch time to Excel-friendly times with 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 the date -d no longer has an @ to represent unix epoch (given how capable the string parser is, I'm actually surprised the @ is mandatory; what other date format has 9-10 digits?) and it's now asked for two outputs: the epoch and the time zone offset. You could therefore use e.g. @1234567890 as an input.
Bug
Lotus 1-2-3 (the original spreadsheet software) intentionally treated 1900 as a leap year despite the fact that it was not (this reduced the codebase at a time when every byte counted). Microsoft Excel retained this bug for compatibility, skipping day 60 (the fictitious 1900/02/29), retaining Lotus 1-2-3's mapping of day 59 to 1900/02/28. LibreOffice instead assigned day 60 to 1900/02/28 and pushed all previous days back one.
Any date before 1900/03/01 could be as much as a day off:
Day Excel LibreOffice
-1 -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 doesn't acknowledge negative dates and has a special definition of the Zeroth of January (1899/12/31) for day zero. Internally, Excel does indeed handle negative dates (they're just numbers after all), but it displays them as numbers since it doesn't know how to display them as dates (nor can it convert older dates into negative numbers). Feb 29 1900, a day that never happened, is recognized by Excel but not LibreOffice.
Because my edits to the above were rejected (did any of you actually try?), here's what you really need to make this work:
Windows (And Mac Office 2011+):
- Unix Timestamp =
(Excel Timestamp - 25569) * 86400 - Excel Timestamp =
(Unix Timestamp / 86400) + 25569
MAC OS X (pre Office 2011):
- Unix Timestamp =
(Excel Timestamp - 24107) * 86400 - Excel Timestamp =
(Unix Timestamp / 86400) + 24107