Date Format

Topics: User Forum
Jun 16, 2013 at 5:57 PM
Hi,

I have a date column. It gets the data as it is on database (2012-08-16). I've set auto-filter on. Filter on Date column (F) has a list but the dates are not grouped in the way normally Excel does. So the user cannot filter data with years or months such as filtering January and February.

I've been checking the topics to find an example to suit my case and actually found a string to turn the column to "Date" format. But can't go any further.

Shortly to say: I want to have 2012-08-16 to be turned to 16.08.2012 in column F and see the Excel's usual filter box.

Thanks in advance.
Coordinator
Jun 16, 2013 at 6:17 PM
Edited Jun 16, 2013 at 6:18 PM
Unless you're using the Advanced Value Binder, then you'll need to convert that date string to an Excel timestamp before storing it in the cell (otherwise you're simply treating it as a string), converting it to a unix timestamp or a PHP DateTime object, then to an Excel timestamp using
$unixTimestamp = strtotime('2012-08-16');
$excelDate = PHPExcel_Shared_Date::PHPToExcel($unixTimestamp);
or
$dateTimeObject = new DateTime('2012-08-16');
$excelDate = PHPExcel_Shared_Date::PHPToExcel($dateTimeObject);
Then set the cell's number format mask to an appropriate Excel numberformat mask
$objPHPExcel->getActiveSheet()
    ->getStyle('C11')
    ->getNumberFormat()->setFormatCode('dd.mm.yyyy');