Excel date to Unix timestamp

Question

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

Answer 1

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)

Answer 2

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.

Answer 3

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

Answer 4

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
   

Answer 5

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);
};

Answer 6

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