phpexceldatephpspreadsheetphpoffice-phpspreadsheet

PhpSpreadsheet Date::PHPToExcel() adding time

I am trying to add a date field into an Excel file without the time portion.

This is my current code:

$dt = !empty($date) ? Date::PHPToExcel(DateTime::createFromFormat('Y-m-d', $date)) : null;
$spreadsheet->getActiveSheet()->setCellValue("A1", $dt);
$spreadsheet->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode('yyyy-mmm-dd');

Somehow time is added to the date and although it isn't visible on what is printed, when you click on the cell, it contains the time portion as well. How do I prevent this from happening as I only want the date?

I also tried:

$dt = !empty($date) ? $date : null;
$spreadsheet->getActiveSheet()->setCellValue("A1", $dt);
$spreadsheet->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode('yyyy-mmm-dd');

But this one shows YYYY-MM-DD even though I set the number format to YYYY-MMM-DD.

Solution

  • Apparently DateTime::createFromFormat() adds the current time if you don't specify it.

    The solution is to add ! in the format to reset all the fields to zero-like values which I got from this comment.

    $dt = !empty($date) ? Date::PHPToExcel(DateTime::createFromFormat('!Y-m-d', $date)) : null;
$spreadsheet->getActiveSheet()->setCellValue("A1", $dt);
$spreadsheet->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode('yyyy-mmm-dd');