php Question posted in
Official manual can be found here.

Negative time values in PhpSpreadsheet

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 PhpOfficePhpSpreadsheetSharedDate 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);
}

  2. 0

    In PhpSpreadsheet version 2.2, the way negative time values are handled has changed, and the previous method with Shared_Date::setExcelCalendar is no longer effective.

    To work with negative time values correctly in Excel using PhpSpreadsheet, you need to use custom number formatting. Excel does not support negative date or time values by default, but you can format them to display correctly with a custom format.

    1. Set the Excel Calendar (optional): If you still need to use the 1904 date system (like the previous CALENDAR_MAC_1904 option), you can do this:

    PhpOfficePhpSpreadsheetSharedDate::setExcelCalendar(PhpOfficePhpSpreadsheetSharedDate::CALENDAR_MAC_1904);

    1. Handle Negative Time Values: Excel will display ############ for negative times unless you apply a custom time format. To show negative time values, use the [hh]:mm:ss format:

    Example for handling negative time:

    $spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet(); $sheet = $spreadsheet->getActiveSheet();

    // Set negative time value
    $negativeTime = -0.5;

    // Set cell value and apply the custom time format
    $sheet->setCellValue('A1', $negativeTime);

    // Time format that handles negative values
    $sheet->getStyle('A1')->getNumberFormat()->setFormatCode('[hh]:mm:ss');

    // Save file or output as needed

    This will allow Excel to show the negative time value correctly instead of ############.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search