Excel date to Unix timestamp

Question

Does anyone know how to convert an Excel date to a correct Unix timestamp

User · Answer

You re apparently off by one day  exactly 86400 seconds  Use the number 2209161600 Not the number 2209075200 If you Google the two numbers  you ll find support for the above  I tried your formula but was always coming up 1 day different from my server   It s not obvious from the unix timestamp unless you think in unix instead of human time  -  but if you double check then you ll see this might be correct

User · Answer

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     nbsp   ldquo Jan Zero  1900 rdquo  is 1899 12 31  see the Bug section below      Excel 2011 for Mac  and older  use the 1904 date system    nbsp   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 gt  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 gt  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

User · Answer

None of the current answers worked for me because my data was in this format from the unix side   2016-02-02 19 21 42 UTC  I needed to convert this to Epoch to allow referencing other data which had epoch timestamps    Create a new column for the date part and parse with this formula    DATEVALUE MID A2 6 2   amp       amp  MID A2 9 2   amp       amp  MID A2 1 4     As other Grendler has stated here already  create another column     B2-DATE 1970 1 1   86400   Create another column with just the time added together to get total seconds      VALUE MID A2 12 2   60 60 VALUE MID A2 15 2   60 VALUE MID A2 18 2     Create a last column that just adds the last two columns together     C2 D2

User · Answer

To make up for the daylight saving time  starting on March s last sunday until October s last sunday  I had to use the following formula    IF    AND      A2 gt  EOMONTH DATE YEAR A2  3 1  0 -MOD WEEKDAY EOMONTH DATE YEAR A2  3 1  0  11  7       A2 lt  EOMONTH DATE YEAR A2  10 1  0 -MOD WEEKDAY EOMONTH DATE YEAR A2  10 1  0  11  7          A2-DATE 1970 1 1 -TIME 1 0 0   24 60 60 1000     A2-DATE 1970 1 1   24 60 60 1000     Quick explanation   If the date   A2   is between March s last sunday and October s last sunday  third and fourth code lines   then I ll be subtracting one hour  -TIME 1 0 0   to the date

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I had an old Excel database with  human-readable  dates  like 2010 03 28  20 12 30 Theese dates were in UTC 1  CET  and needed to convert it to epoch time   I used the   A4-DATE 1970 1 1   86400-3600 formula to convert the dates to epoch time from the A column to B column values  Check your timezone offset and make a math with it  1 hour is 3600 seconds   The only thing why i write here an anwser  you can see that this topic is more than 5 years old is that i use the new Excel versions and also red posts in this topic  but they re incorrect  The DATE 1970 1 1   Here the 1970 and the January needs to be separated with   and not with    If you re also experiencing this issue  hope it helps you  Have a nice day

User · Answer

Here s my ultimate answer to this   Also apparently javascript s new Date year  month  day  constructor doesn t account for leap seconds too      Parses an Excel Date   serial   into a    corresponding javascript Date in UTC 0 timezone        Doesn t account for leap seconds     Therefore is not 100  correct     But will do  I guess  since we re    not doing rocket science here        https   www pcworld com article 3063622 software mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more html     If you need to calculate dates in your spreadsheets      Excel uses its own unique system  which it calls Serial Numbers      lib parseExcelDate   function  excelSerialDate          Excel serial date  is just      the count of days since  01 01 1900        seems that it may be even fractional             The count of days elapsed      since  01 01 1900   Excel epoch       till  01 01 1970   Unix epoch        Accounts for leap years       19 of them  yielding 19 extra days     const daysBeforeUnixEpoch   70   365   19        An hour  approximately  because a minute      may be longer than 60 seconds  see  leap seconds     const hour   60   60   1000         In the 1900 system  the serial number 1 represents January 1  1900  12 00 00 a m        while the number 0 represents the fictitious date January 0  1900        These extra 12 hours are a hack to make things      a little bit less weird when rendering parsed dates       E g  if a date  Jan 1st  2017  gets parsed as       Jan 1st  2017  00 00 UTC  then when displayed in the US      it would show up as  Dec 31st  2016  19 00 UTC-05   Austin  Texas        That would be weird for a website user       Therefore this extra 12-hour padding is added      to compensate for the most weird cases like this       doesn t solve all of them  but most of them        And if you ask what about -12  12 border then      the answer is people there are already accustomed      to the weird time behaviour when their neighbours      may have completely different date than they do             Math round    rounds all time fractions      smaller than a millisecond  e g  nanoseconds       but it s unlikely that an Excel serial date      is gonna contain even seconds         return new Date Math round  excelSerialDate - daysBeforeUnixEpoch    24   hour    12   hour

[excel] Excel date to Unix timestamp

Examples related to excel