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.
This oneliner worked for me, using PHPExcel of course.
You clearly haven't looked very hard:
Taken directly from the PHPExcel Date handling code:
Set self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900 as necessary to indicate the Excel base calendar that you're using: Windows 1900 or Mac 1904
and if you want a PHP DateTime object instead:
I had same issue on a project, I was looking for a
PHP Class to Read Excel as PHP Array
. I was lucky that I found'SimpleXLSX' Class
. It can handle excel data very well, but.. but... ohh!! but.... :( I have realised that there is some issue withDate Reading From Excel
field. In excel values was looking fine but when we were trying to import them, The Date Values Getting Different. Some time we were getting correct values some times just a number and some times a float values. We were looking for solution '' Then after the lots of goggling we found the reason:
@Source: reading xls date in php
@Source: Excel date conversion using PHP Excel
So, I made a little helper class to read Excel Date for use in PHP. I hope it will help someone and will reduce goggling and efforts.
Here is my code to
Read Excel as PHP Array()
andParse Excel Date as PHP Date
SimpleXLSX.php
from given example codeHope this code will help someone. I was struggling same problem So I made this little script to save others time.
Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:
and to convert from Unix date to Excel date, use this formula:
After doing this formula into a variable, you can get the real date in PHP using this example:
Thanks.