Convert the FULL Excel date serial format to Unix timestamp

Question

I've seen lots of references to converting the "date" portion of the Excel serial date format, but everyone seems to skip the "time" portion of it.

Here is what I need to do:

I have an Excel file I'm importing. PHP in use.

I am encountering the Excel Date Serial format (dddddd.tttttt) and need to convert this to an entire Unix timestamp.

I've tried a few different things, but am getting hung up on how to do this in a fluid motion.

Answer 1

Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

and to convert from Unix date to Excel date, use this formula:

EXCEL_DATE = 25569 + (UNIX_DATE / 86400)

After doing this formula into a variable, you can get the real date in PHP using this example:

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

Thanks.