Excel Date Conversion Using PHP Excel

PHPExcel: how to set date format for a cell

$sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16");

Sets a string value in the cell, not a date. Just because you interpret that as a date, doesn't mean that computer programs automagically interpret it as a date.

Look at the date Examples in the PHPExcel Documentation and Examples, and you'll see that you need to set the cell value to a MS Excel serialized timestamp (a float value of the number of days since 1st January 1900). You can use the PHPExcel functions like PHPExcel_Shared_Date::PHPToExcel() to convert human dates/PHP DateTime objects/Unix timestamps to MS Excel Serialized timestamps.

$sheet->setCellValueByColumnAndRow(0, 1, PHPExcel_Shared_Date::PHPToExcel( '2014-10-16' ));

Once you've stored the value as a timestamp, you can then apply whatever date format mask you want to that cell to get your desired formatting

Read Date Format in PHP EXCEL

When you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'?

Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you.

You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
or any of the other format masks in
PHPExcel_Style_NumberFormat
, or convert it to a PHP date using
PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date()
function to format it as you wish

Example:

$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

Convert Excel's 41014 date to actual date in PHP or JavaScript

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

Change date format during conversion from xlsx to csv with PHPExcel

I was looking for a way to set the format code for all cells that are dates without having to iterate through all the cells. I never found that solution. Here is what I ended up using.

$filePath = "fromFile.xlsx"; 
$csvPath = "toFile.csv";

PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$reader = PHPExcel_IOFactory::load( $filePath );

foreach( $reader->getActiveSheet()->getRowIterator(1) as $row ){
    foreach( $row->getCellIterator() as $cell ){
        if( PHPExcel_Shared_Date::isDateTime($cell)){
            $cell->getStyle()->getNumberFormat()->setFormatCode("mm/dd/yyyy" );
        }
    }
}

$writer = PHPExcel_IOFactory::createWriter( $reader, 'CSV' );
$writer->setDelimiter(",");
$writer->setEnclosure('"');
$writer->setSheetIndex(0);

$writer->save( $csvPath );

PHP function for convert date time to excel Number DATEVALUE conversion

I found a way to convert a Unix timestamp to an Excel date.

$date_time = "2013-11-01 00:00:00";
$date_time_plus_one = strtotime($date_time . ' +1 day');
$str_date = strtotime(date('Y-m-d', $date_time_plus_one));
$excel_date = intval(25569 + $str_date / 86400);

echo 'php actual date time : ' . $date_time . '<br>';
echo 'add one day : ' . $date_time_plus_one . '<br>';
echo 'excel Number DATEVALUE : ' . $excel_date . '<br>';

seconds in a day: 86400 , 25569 days between 30 Dec 1899 and 01 Jan 1970. So This is the output.

php actual date time : 2013-11-01 00:00:00
add one day : 1383330600
excel Number DATEVALUE : 41579

PHPExcel - Get date with forced format

You can return the raw value from the cell using the getValue() method,

$excelTimestampValue = $objPHPExcel->getActiveSheet()
    ->getCell('A12')
    ->getValue();

which will return an MS Excel serialized timestamp value; that is, a floating point number representing the number of days since 1st January 1900 (or 1st January 1904, depending on which calendar the spreadsheet file is using). From that timestamp value, you have a number of options:

Convert that timestamp to a unix timestamp, which you can then format however you wish using PHP's date() function.

$unixTimestamp = PHPExcel_Shared_Date::ExcelToPHP($excelTimestampValue);
echo date('d-M-Y', $unixTimestamp);

Convert that timestamp to a PHP DateTime object, which you can then format however you wish using the format() method.

$dto = PHPExcel_Shared_Date::ExcelToPHPObject($excelTimestampValue);
echo $dto->format('d-M-Y');

Format it directly to a string, using an MS Excel date format mask.

echo PHPExcel_Style_NumberFormat::toFormattedString($excelTimestampValue, 'dd-mmm-yyyy');