[FIXED] how generate date format using PhpExcel?

Issue

$date='30-09-11'
  $objPHPExcel->getActiveSheet()
   ->setCellValue(toAlphaNumber($tmpCOunt + 9) . $rowNbmr, $date)
   ->getStyle(toAlphaNumber($tmpCOunt + 9) . $rowNbmr )->applyFromArray( array( 'font'  => array('color' => array('rgb' => '000000')) ))
    ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14 )

                    ;

on Excel the cell shows that it is a "Date" but I can't manipulate it before I select the cell and press any key, then automatically it will align to the right and I become able to manipulate as a date.

Solution

'30-09-11' is not a date. It might look like a date to a human; but to a computer it is nothing more than a string. To tell PHPExcel that it is a date, you have to convert it to something that the computer will recognise as a date - in this case, a MS Excel serialized timestamp (which is basically a count of the number of days since 1st January 1900.

PHPExcel provides functionality too do just that, namely the PHPToExcel() method in the PHPExcel_Shared_Date class.

However, you may also need to explain what your date string represents, e.g. is it 30th September 1911 or 2011? Is it 11th September 1930 or 2030? You need to sort out that ambiguity, because neither PHP nor PHPExcel cannot automagically understand what you mean by this ambiguous string, even when you do tell them that it's a date.

Answered By - Mark Baker

Answer Checked By - Mildred Charles (PHPFixing Admin)