最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

Convert Excel's "41014" date to actual date in PHP or JavaScript - Stack Overflow

programmeradmin8浏览0评论

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
Add a ment  | 

4 Answers 4

Reset to default 3

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
发布评论

评论列表(0)

  1. 暂无评论