CODEWITHSUNDEEP
javascriptphpjquerymysqlexcel
Brian Powell
Brian Powell

Reputation: 3411

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

}

Upvotes: 5

Views: 6461

Answers (4)

Akshay Chandran
Akshay Chandran

Reputation: 1353

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

Upvotes: 0

wizjo
wizjo

Reputation: 13

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

Upvotes: 0

LarsBauer
LarsBauer

Reputation: 1535

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.

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212412

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

Upvotes: 3

Related Questions