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

}

• javascript
• php
• jquery
• mysql
• excel

4 Answers 4

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()

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