Search code examples
phpexceldatephpexcel

PHPExcel - Get date with forced format

I use PHPexcel in a php application. I used ->getFormattedValue() to get date from excel worksheet.

When I use excel file A it gives me the following date format: 05/11/2016 But with the file B it gives following format : 05-Nov-17

How to force a date format in order to get a format like this 05-Nov-17 all the time? How to convert it directly from PHPExcel to PHP date?

Solution

  • 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');