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 es 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
}
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 es 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
}
Share
Improve this question
edited May 18, 2015 at 15:24
Brian Powell
asked May 18, 2015 at 14:19
Brian PowellBrian Powell
3,4114 gold badges37 silver badges64 bronze badges
4 Answers
Reset to default 3Taken 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()
If you prefer JavaScript, I have found the following formula on GitHub:
new Date((excelDate - (25567 + 2))*86400*1000)
Works for your given date. Maybe you can give it a try whether it returns the correct date for other values.
Using what @QueryLars suggested this is the simplest working solution in PHP:
$excelDate = 43407; //2018-11-03
$miliseconds = ($excelDate - (25567 + 2)) * 86400 * 1000;
$seconds = $miliseconds / 1000;
echo date("Y-m-d", $seconds); //2018-11-03
Those 5 digit Numbers I think are dates in Julian format, That must be the numerical universal way of expressing date and Time,
For Eg Jul. number 36309
=> 1999-05-29
(mon Database date format)
number 44225.403368056
=> 2021-01-29 09:40:51
(For date-time, the fractions represent time)
To do this conversion, using language php here, but it can be changed to any language of preference with use of similar built in functions
function correctDateTime($dateTime)
{
# integer digits for Julian date
$julDate = floor($dateTime);
# The fractional digits for Julian Time
$julTime = $dateTime - $julDate;
# Converts to Timestamp
$timeStamp = ($julDate > 0) ? ($julDate - 25569) * 86400 + $julTime * 86400 : $julTime * 86400;
# php date function to convert local time
return [
"Date-Time"=>date("Y-m-d H:i:s", $timeStamp),
"Date"=>date("Y-m-d", $timeStamp),
"Time"=>date("H:i:s", $timeStamp)
];
}
$formattedValue = correctDateTime("44225.403368056");
echo $formattedValue["Date-Time"] . "\n"; // 2021-01-29 09:40:51
echo $formattedValue["Date"] . "\n"; // 2021-01-29
echo $formattedValue["Time"] . "\n"; // 09:40:51