Why does PHPexcel insert a single quote in a date field?

Question

I am trying to insert a date into a cell using PHPExcel. This is my code:

    include('xlsx/Classes/PHPExcel.php');
    include('xlsx/Classes/PHPExcel/Calculation.php');
    include('xlsx/Classes/PHPExcel/Cell.php');
    $objPHPExcel = new PHPExcel();
    $start = 3;
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode('DD.MM.YYYY');
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', date('d.m.Y', time()+60*($start + 3)));
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode('DD.MM.YYYY');
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->save('text.xls');

However, PHPexcel selects the correct format, but inserts a date with a single quote

'27.02.2016

instead

27.02.2016

I do not know how to insert a date without this quote, I hope that you will help me.

Answer 1

The method setFormatCodesets the format that Excel will use to display the date, instead of setting the PHPExcel format to interpret the date. Excel tables store dates in a specific timestamp format that can be generated using the class PHPExcel_Shared_Date. It has methods for converting various date formats and the expected Excel format.

$objPHPExcel->getActiveSheet()->SetCellValue('A1',
    PHPExcel_Shared_Date::PHPToExcel(time()+60*($start + 3)));

... .

Answer 2

:

  $objPHPExcel->getActiveSheet()->SetCellValue('A1', '=NOW()+0.00001*60*'.$start);