Convert Excel’s “41014” date to actual date in PHP or JavaScript

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

}

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