Convert the FULL Excel date serial format to Unix

I've seen lots of references to converting the "date" portion of the Excel serial date format, but everyone seems to skip the "time" portion of it.

Here is what I need to do:

I have an Excel file I'm importing. PHP in use.

I am encountering the Excel Date Serial format (dddddd.tttttt) and need to convert this to an entire Unix timestamp.

I've tried a few different things, but am getting hung up on how to do this in a fluid motion.

回答1:

Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

and to convert from Unix date to Excel date, use this formula:

EXCEL_DATE = 25569 + (UNIX_DATE / 86400)

After doing this formula into a variable, you can get the real date in PHP using this example:

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

Thanks.

回答2:

This oneliner worked for me, using PHPExcel of course.

$date_formated = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_int_val));

回答3:

You clearly haven't looked very hard:

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

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

回答4:

I had same issue on a project, I was looking for a PHP Class to Read Excel as PHP Array. I was lucky that I found 'SimpleXLSX' Class. It can handle excel data very well, but.. but... ohh!! but.... :( I have realised that there is some issue with Date Reading From Excel field. In excel values was looking fine but when we were trying to import them, The Date Values Getting Different. Some time we were getting correct values some times just a number and some times a float values. We were looking for solution '

WHY IT IS HAPPENING, WHY PHP NOT GETTING CORRECT DATE FROM EXCEL

' Then after the lots of goggling we found the reason:

@Source: reading xls date in php

According to excel format 41397 is 2013-05-03 Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. This is called a serial date, or serial date-time.

@Source: Excel date conversion using PHP Excel

Convert Excel Date To Unix Date and Then Convert Unix Date to PHP Date

So, I made a little helper class to read Excel Date for use in PHP. I hope it will help someone and will reduce goggling and efforts.

Here is my code to Read Excel as PHP Array() and Parse Excel Date as PHP Date

For Beginners:

Download SimpleXLSX.php from given example code
Convert excel data (field/value) in PHP array()
Convert excel date into PHP
Now.. Yeah!! Excel Data is Ready as an PHP Array to moving into MySQL table...

Here is PHP Code:

<?php   
/*
  EXCEL DATA EXAMPLE:
  ----------------------------
  ID      Consumer_Date  Delivery_Date   Date_of_Dispatch    Gift_Date_Created   Active_Date              Approved_Date
  536     -No-Data-      9-Nov-15        7-Nov-15            -No-Data-           10/31/2015 12:00:00 AM   4/11/2015 10:21
  537     -No-Data-      -No-Data-       7-Nov-15            -No-Data-           10/23/2015 12:00:00 AM   3/11/2015 16:24

*/

/*
  EXCEL DATA IN PHP ARRAY FORMAT
  -------------------------------
  Array
  (
      [0] => Array
          (
              [ID] => 536
              [Consumer_Date] => -No-Data-
              [Delivery_Date] => 42317
              [Date_of_Dispatch] => 42315
              [Gift_Date_Created] => -No-Data-
              [Active_Date] => 10/31/2015 12:00:00 AM
              [Approved_Date] => 42105.431574074
          )
      [1] => Array
          (
              [ID] => 537
              [Consumer_Date] => -No-Data-
              [Delivery_Date] => -No-Data-
              [Date_of_Dispatch] => 42315
              [Gift_Date_Created] => -No-Data-
              [Active_Date] => 10/23/2015 12:00:00 AM
              [Approved_Date] => 42074.683958333
          )
  )

*/

/* ----------------- */
/* Excel_Date_Parser.php */
/* ----------------- */


// Numbers of days between January 1, 1900 and 1970 (including 19 leap years)
define("MIN_DATES_DIFF", 25569);

// Numbers of second in a day:
define("SEC_IN_DAY", 86400);

/** Set default timezone (will throw a notice otherwise) */
date_default_timezone_set('Asia/Kolkata');

/**
 * Class Excel_Date_Parser
 *
 * SetDateString($excel_date_value) : send excel date column value
 * GetDateString(): get your php date in Y-m-d format (MySQL)
 */
class Excel_Date_Parser
{

  /**
   * Send Excel Date String Value Here
   * @param [type] $date_from_excel [description]
   * @return instance Excel_Date_Parser
   */
  public function SetDateString($date_from_excel) {
    $this->date_from_excel = $date_from_excel;
    return $this;
  }

  /**
   * Set Date Format Here, default is "Y-m-d"
   * @param string $set_format_date [description]
   */
  public function SetDateFormat($set_format_date = "Y-m-d") {
    $this->set_format_date = $set_format_date;
  }

  /**
   * Get what format is set
   */
  public function GetDateFormat() {
    return $this->set_format_date;
  }

  /**
   * Return PHP date according to Set Format from Excel Date
   * @return string php date
   */
  public function GetDateString() {

    // if value is valid date string
    if (strtotime($this->date_from_excel)) {

      /**
       * Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
       * plus a fractional portion of a 24 hour day: ddddd.tttttt.
       * This is called a serial date, or serial date-time.
       *
       * @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
       */
      if (is_float($this->date_from_excel)) {

        // date format 2015-25-12
        $this->SetDateFormat("Y-d-m");
        $this->date_from_excel = date($this->GetDateFormat() , (mktime(0, 0, -1, 1, $this->date_from_excel, 1900)));
      } 
      else {

        // date format 2015-12-25
        $this->SetDateFormat();

        // return converted date string in php format date format 2015-12-25
        $this->date_from_excel = date($this->GetDateFormat() , strtotime($this->date_from_excel));
      }
    }

    /**
     * Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
     * plus a fractional portion of a 24 hour day: ddddd.tttttt .
     * This is called a serial date, or serial date-time.
     *
     * According to excel format 41397 is 2013-05-03
     * @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
     */
    else if (is_integer($this->date_from_excel)) {
      $this->SetDateFormat();
      $this->date_from_excel = gmdate($this->GetDateFormat() , (($this->date_from_excel - MIN_DATES_DIFF) * SEC_IN_DAY));
    }

    // return real value
    else {
      $this->date_from_excel = $this->date_from_excel;
    }

    // return date
    return $this->date_from_excel;
  }
}


/* ----------------- */
/* Excel_Reader.php */
/* ----------------- */

/* Show errors */
error_reporting(1);

/* display error */
ini_set('display_errors', 1);

/**
* Using class SimpleXLSX 
* 
* Big Thanks!!!! to Sergey Shuchkin, Who made Excel Reader Class
* 
* This class can be used to parse and retrieve data from Excel XLS spreadsheet files.
* It can parse a given Excel XLS file by extracting its contents files and parsing the 
* contained XML spreadsheet file.
*
* The class provides functions to retrieve data for the spreadsheet worksheets, rows and cells.
*
* @link: http://www.phpclasses.org/package/6279-PHP-Parse-and-retrieve-data-from-Excel-XLS-files.html
* @author: Sergey Shuchkin
* @download: http://www.phpclasses.org/browse/download/zip/package/6279/name/simple-xlsx-2013-10-13.zip
*/
require_once 'SimpleXLSX.php';


/* Adding my class Excel_Date_Parser */
require_once 'Excel_Date_Parser.php';


/**
 * [toPhpDate description]
 * @param [type] $array [description]
 */
function toPhpDate($array) {

  // create class object
  $ed = new Excel_Date_Parser();

  // parse array and set
  $array['Consumer_Date'] = $ed->SetDateString($array['Consumer_Date'])->GetDateString();
  $array['Delivery_Date'] = $ed->SetDateString($array['Delivery_Date'])->GetDateString();
  $array['Date_of_Dispatch'] = $ed->SetDateString($array['Date_of_Dispatch'])->GetDateString();
  $array['Gift_Date_Created'] = $ed->SetDateString($array['Gift_Date_Created'])->GetDateString();
  $array['Active_Date'] = $ed->SetDateString($array['Active_Date'])->GetDateString();
  $array['Approved_Date'] = $ed->SetDateString($array['Approved_Date'])->GetDateString();

  // return php array
  return $array;
}

// make xls object
$xlsx = new SimpleXLSX('Sony_RedemptionFormat 8-Dec-15.xlsx');

// get excel data as array
$Excel_Array_Data = $xlsx->rows();

// Get Column Name
$Excel_Column = $Excel_Array_Data[0];

// Remove Column Name From Array
unset($Excel_Array_Data[0]);

// Rest Data is Excel Data without Column
$Excel_Data = $Excel_Array_Data;

// Combine array for inserting in database
foreach ($Excel_Array_Data as $key => $Excel_Data) {
  $insert_data[] = array_combine($Excel_Column, $Excel_Data);
}

// show array data
echo "<pre>", print_r($insert_data, true);

// update array excel date
$insert_data = array_map('toPhpDate', $insert_data);

// show array data after update date
echo "<pre>", print_r($insert_data, true);

Hope this code will help someone. I was struggling same problem So I made this little script to save others time.