I’m writing something in PHP/Jquery that allows the user to upload an Excel spreadsheet from Excel. It then takes the data in that spreadsheet and assigns the values from each cell into a variable, but for some reason, I’m having a hard time with dates. Any date in Excel comes in as a number, like 41014
, instead of 04/15/2012
(as if I were to format in Excel as text
).
How do I convert this to a YYYY-MM-DD format so it fits in with the rest of the mySQL database I’m working with? I can do it either in PHP or Jquery, but doing it in jQuery seems easier to me.
Excel’s Cell
04/15/2012
PHP’s echo json_encode($var);
`{dateReceived: 41014}`
jQuery’s console.log(dateReceived);
41014
Update
I couldn’t actually get either of the answers provided here to work – I thought the php answer worked initially, but for some reason I couldn’t get it to output what I needed, but I found another simple formula that I put into a function. In case anyone else is looking for an answer to a similar question, here’s what I did:, (where $dateValue is the Excel Date 41014
etc…)
function convertDate($dateValue) {
$unixDate = ($dateValue - 25569) * 86400;
return gmdate("Y-m-d", $unixDate);
'where Y is YYYY, m is MM, and d is DD
}
Advertisement
Answer
Taken directly from the PHPExcel Date handling code:
public static function ExcelToPHP($dateValue = 0) {
if (self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900) {
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
} else {
$myExcelBaseDate = 24107;
}
// Perform conversion
if ($dateValue >= 1) {
$utcDays = $dateValue - $myExcelBaseDate;
$returnValue = round($utcDays * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer) $returnValue;
}
} else {
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer) gmmktime($hours, $mins, $secs);
}
// Return
return $returnValue;
} // function ExcelToPHP()
Set self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900 as necessary to indicate the Excel base calendar that you’re using: Windows 1900 or Mac 1904… most likely 1900
and if you want a PHP DateTime object instead:
public static function ExcelToPHPObject($dateValue = 0) {
$dateTime = self::ExcelToPHP($dateValue);
$days = floor($dateTime / 86400);
$time = round((($dateTime / 86400) - $days) * 86400);
$hours = round($time / 3600);
$minutes = round($time / 60) - ($hours * 60);
$seconds = round($time) - ($hours * 3600) - ($minutes * 60);
$dateObj = date_create('1-Jan-1970+'.$days.' days');
$dateObj->setTime($hours,$minutes,$seconds);
return $dateObj;
} // function ExcelToPHPObject()