After using non-UTC timezone HOUR Calculated wrong value

[xandros15]

This is:

• a bug report
• a feature request
• not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

When use Date::setDefaultTimezone(); with timezone other than UTC, function 'HOUR' should return correct value.

What is the current behavior?

When use Date::setDefaultTimezone(); with timezone other than UTC, function 'HOUR' return incorrect value.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

require_once __DIR__ . '/vendor/autoload.php';

//with Europe/London
$timezone1 = new DateTimeZone('Europe/London');
Date::setDefaultTimezone($timezone1);
$spreadsheet1 = new Spreadsheet();
$worksheet1 = $spreadsheet1->getActiveSheet();
Calculation::getInstance($spreadsheet1)->disableCalculationCache();
$worksheet1->fromArray([
    [
        Date::dateTimeToExcel(DateTime::createFromFormat('d.m.Y H:i:s', '01.03.2021  10:45:00', $timezone1)),
        Date::dateTimeToExcel(DateTime::createFromFormat('d.m.Y H:i:s', '01.03.2021  11:00:00', $timezone1)),
        '=B1-A1',
        '=(INT(C1)*24)+HOUR(MOD(C1,1))+(MINUTE(MOD(C1,1))/60)',
    ],
], null, 'A1');
echo $worksheet1->getCell('D1')->getCalculatedValue(true), PHP_EOL;

//with UTC
$timezone2 = new DateTimeZone('UTC');
Date::setDefaultTimezone($timezone2);
$spreadsheet2 = new Spreadsheet();
$worksheet2 = $spreadsheet2->getActiveSheet();
Calculation::getInstance($spreadsheet2)->disableCalculationCache();
$worksheet2->fromArray([
    [
        Date::dateTimeToExcel(DateTime::createFromFormat('d.m.Y H:i:s', '01.03.2021  10:45:00', $timezone2)),
        Date::dateTimeToExcel(DateTime::createFromFormat('d.m.Y H:i:s', '01.03.2021  11:00:00', $timezone2)),
        '=B1-A1',
        '=(INT(C1)*24)+HOUR(MOD(C1,1))+(MINUTE(MOD(C1,1))/60)',
    ],
], null, 'A1');


echo $worksheet2->getCell('D1')->getCalculatedValue(true), PHP_EOL;

results:

23.25
0.25

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.4.8

[oleibman]

Which version of PhpSpreadsheet? I ran your code without any changes against the current version and saw:

0.25
0.25

I believe that is the expected result. I used PHP 7.4.14, as well as versions of 8.0, 7.3, 7.2, and 7.1.

[xandros15]

1.17,1

[oleibman]

Agreed - 1.17.1 shows this problem, but master does not! So I guess this is just a question of waiting for the code in master to become official. I have not yet identified the change which fixed this. I'll keep looking.

[oleibman]

The problem had been in the implementation of the HOUR function. It was fixed by PR #1937, which was merged on March 21, about 19 days after 1.17.1 was released.