Convert Excel "41014" date to actual date in PHP or JavaScript
I am writing something in PHP / Jquery that allows the user to download an Excel spreadsheet from Excel. It then takes the data in that spreadsheet and assigns the values from each cell to a variable, but for some reason I'm having a hard time spending time with the dates. Any date in Excel comes in as a number, for example 41014
, not 04/15/2012
(as if I need to format in Excel as text
).
How do I convert this to YYYY-MM-DD format so that it matches the rest of the mySQL database I'm working with? I can do this in either PHP or JQuery, but I find it easier to do it in jQuery.
Excel cell
04/15/2012
PHP echo json_encode($var);
`{dateReceived: 41014}`
jQuery console.log(dateReceived);
41014
Update
I couldn't get any 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: put in a function. If 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
}
source to share
Executed directly from PHPExcel Date processing 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 if needed to specify the underlying Excel calendar you are using: Windows 1900 or Mac 1904 ... most likely 1900
and if you want PHP DateTime 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()
source to share