Convert Excel dates into PHP timestamps
Having trouble getting your PHP script to build dates from Excel using mktime() ? Me too. Everything’s one day off? I know it. Here’s what I found.
The proper way to convert Excel dates into PHP-friendly timestamps using mktime():
PHP Code:
// The date 6/30/2009 is stored as 39994 in Excel
$days = 39994;
// But you must subtract 1 to get the correct timestamp
$ts = mktime(0,0,0,1,[B]$days-1[/B],1900);
// So, this would then match Excel’s representation:
echo date(“m/d/Y”,$ts);
Excel uses “number of days since Jan. 1, 1900″ to store its dates. It also treats 1900 as a leap year when it wasn’t, thus there is an extra day which must be accounted for in PHP (and the rest of the world). Subtracting 1 from Excel’s number will fix this problem.