Negative time values

[mcurtaz]

Our software is designed to track work hours, so we need to generate Excel files that includes 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:

\PhpOffice\PhpSpreadsheet\Shared\Date::setExcelCalendar(\PhpOffice\PhpSpreadsheet\Shared\Date::CALENDAR_MAC_1904);

Recently we upgraded to version 2.2 and this configuration no longer works.

Is there any alternative solution?

[oleibman]

This appears to have been an oversight from when we added the ability to set the calendar by spreadsheet rather than globally. You should still be able to get the result you want as follows:

        if (method_exists($spreadsheet, 'setExcelCalendar')) {
            $spreadsheet->setExcelCalendar(Date::CALENDAR_MAC_1904);
        } else {
            Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
        }

What a horrible kludge for MS to have inflicted on its users.