Excel date to Unix timestamp

Does anyone know how 开发者_如何学运维to convert an Excel date to a correct 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

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.

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 :)

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.

1. Create a new column for the date part and parse with this formula

   =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
   

2. As other Grendler has stated here already, create another column

   =(B2-DATE(1970,1,1))*86400 
   

3. 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)))
   

4. Create a last column that just adds the last two columns together:

   =C2+D2
   

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>=EOMONTH(DATE(YEAR(A2);3;1);0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR(A2);3;1);0);11);7);
    A2<=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.

If you can create a custom function (User Defined Function : UDF) : create a VBA module and copy these codes :

Function unix_time(Optional my_year, Optional my_month, Optional my_day, Optional my_hour, Optional my_minute, Optional my_second)
    Dim now_date, now_time, ref_date, ref_time

    my_year = IIf(IsMissing(my_year) = False, my_year, year(Now))
    my_month = IIf(IsMissing(my_month) = False, my_month, month(Now))
    my_day = IIf(IsMissing(my_day) = False, my_day, day(Now))

    my_hour = IIf(IsMissing(my_hour) = False, my_hour, hour(Now))
    my_minute = IIf(IsMissing(my_minute) = False, my_minute, minute(Now))
    my_second = IIf(IsMissing(my_second) = False, my_second, second(Now))

    now_date = DateSerial(my_year, my_month, my_day)
    now_time = TimeSerial(my_hour, my_minute, my_second)

    ref_date = DateSerial(1970, 1, 1)
    ref_time = TimeSerial(0, 0, 0)'You can change this line based on your time zone. for example : Tehran -> TimeSerial(3,30,0)

    now_date = now_date + now_time

    ref_date = ref_date + ref_time

    unix_time = (now_date - ref_date) * 86400
End Function

Then type in any cell you want: =unix_time()

Above code returns current date and time in unix format. If you need custom date and time, pass 6 parameters to "unix_time()" function.

Example : =unix_time(year, month, day, hour, minute, second)

Here's a javascript implementation.

// Parses an Excel Date ("serial") into a corresponding javascript Date in UTC+0 timezone.
// (with time equal to 00:00)
//
// 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".
//
export default function parseExcelDate(excelSerialDate, options) {
  // https://support.microsoft.com/en-gb/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
  if (options && options.epoch1904) {
    excelSerialDate += 1462
  }

  // "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, due to "leap seconds".
  //
  // Still, Javascript `Date` (and UNIX time in general) intentionally
  // drops the concept of "leap seconds" in order to make things simpler.
  // So it's fine.
  // https://stackoverflow.com/questions/53019726/where-are-the-leap-seconds-in-javascript
  //
  // "The JavaScript Date object specifically adheres to the concept of Unix Time
  //  (albeit with higher precision). This is part of the POSIX specification,
  //  and thus is sometimes called "POSIX Time". It does not count leap seconds,
  //  but rather assumes every day had exactly 86,400 seconds. You can read about
  //  this in section 20.3.1.1 of the current ECMAScript specification, which states:
  //
  //  "Time is measured in ECMAScript in milliseconds since 01 January, 1970 UTC.
  //   In time values leap seconds are ignored. It is assumed that there are exactly
  //   86,400,000 milliseconds per day."
  //
  //  The fact is, that the unpredictable nature of leap seconds makes them very
  //  difficult to work with in APIs. One can't generally pass timestamps around
  //  that need leap seconds tables to be interpreted correctly, and expect that
  //  one system will interpret them the same as another. For example, while your
  //  example timestamp 1483228826 is 2017-01-01T00:00:00Z on your system,
  //  it would be interpreted as 2017-01-01T00:00:26Z on POSIX based systems,
  //  or systems without leap second tables. So they aren't portable.
  //  Even on systems that have full updated tables, there's no telling what those
  //  tables will contain in the future (beyond the 6-month IERS announcement period),
  //  so I can't produce a future timestamp without risk that it may eventually change.
  //
  //  To be clear - to support leap seconds in a programming language, the implementation
  //  must go out of its way to do so, and must make tradeoffs that are not always acceptable.
  //  Though there are exceptions, the general position is to not support them - not because
  //  of any subversion or active countermeasures, but because supporting them properly is much,
  //  much harder."
  //
  // https://en.wikipedia.org/wiki/Unix_time#Leap_seconds
  // https://en.wikipedia.org/wiki/Leap_year
  // https://en.wikipedia.org/wiki/Leap_second
  //
  const hour = 60 * 60 * 1000

  return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour))
}