Our software is designed to track work hours, so we need to generate Excel files that include negative time values.
According to the PhpSpreadsheet documentation:
Note that the baseline date itself is day 1; so strictly speaking, the base date 0 is '1899-12-31': Excel considers any value between 0 and 1 as purely a time value. Attempting to display 0 using a date format mask like 'yyyy-mm-dd' will result in an invalid date like '1900-01-00' rather than '1899-12-31.' However, when using a time format mask like 'hh:mm:ss,' it will appear as '00:00:00' (midnight). Values less than 0 are invalid as dates or times, so a negative value in a cell with a date 'Number Format Mask' will display as '############' in Excel.
Open/LibreOffice and Gnumeric don't have this limitation, and negative date/timestamp values are recognized and formatted correctly. However, it is recommended not to rely on this behavior when working with PhpSpreadsheet."
In version 1.19, we resolved this issue by configuring the following setting:
use \PhpOffice\PhpSpreadsheet\Shared\Date as Shared_Date;
Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_MAC_1904);
Recently we upgraded to version 2.2 and this configuration no longer works.
Is there any alternative solution?
As suggested here: https://github.com/PHPOffice/PhpSpreadsheet/discussions/4265#discussioncomment-11488292
The solution is:
if (method_exists($spreadsheet, 'setExcelCalendar')) {
$spreadsheet->setExcelCalendar(Date::CALENDAR_MAC_1904);
} else {
Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
}