Convert the FULL Excel date serial format to Unix timestamp

php import-from-excel
timebinder picture timebinder · Jun 23, 2012 · Viewed 28.2k times · Source

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

Ahmed Eissa picture Ahmed Eissa · Feb 28, 2013

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.

Related questions

Reading an Excel file in PHP

I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed. Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel …

Read More
Excel date conversion using PHP Excel

i am reading date from excel which is in this format 12/5/2012 day/month/year using this code to read . using PHP EXCEL PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' ); its working like charm converting the above date '12/5/2012' to …

Read More
How do I read sheet two of an xlsx file with PHPExcel?

I know how to read my xlsx spreadsheet and loop through the first sheet. It has 5 sheets and I am having trouble getting to any other than the first. Here is the code I am using which was straight from …

Read More