Native Excel dates that happen to be a round number getting mistyped

[orcinus]

This is:

- [X] 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?

All native Excel dates parsed from an Excel spreadsheet should be cast into a float.

What is the current behavior?

Native Excel dates that just so happen to be a round number end up cast into an integer, while those that are not round are correctly cast as float.

What are the steps to reproduce?

1. create a new spreadsheet
2. write "08/03/2020 00:00:00" into first cell
3. write "07/03/2020 07:00:00" into second cell
4. parse the spreadsheet
5. apply getCalculatedValue() on first and second cell
6. first cell's value gets typed as an integer
7. second cell's value gets typed as a float

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:

Not possible, the problem occurs when reading/parsing a file.

Which versions of PhpSpreadsheet and PHP are affected?

It started after 1.8.2. Sadly, i cannot be more precise than that. I was working on a stale branch with 1.8.2 when i got a ticket about an issue that ended up being caused by this bug in production. After hunting around for an hour trying to figure out what's going on, as i couldn't repro the case on my (stale) branch, i realized it might not be our code, updated my PhpSpreadsheet to 1.11.0 and the problem showed up.

We're running PHP 7.1.33

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[orcinus]

Bump.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[orcinus]

Bump again.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[orcinus]

So, how's the weather up there?

[pittyplatsch]

Hi there! On my personal #hacktoberfest quest I came across this issue. Unfortunately couln't reproduce. Can you maybe provide the file you're trying to read?

[alexgunkel]

I could reproduce the behavior. Depending on your file you get a numeric value that either contains an integer or a floating point number. Therefor, it's interpreted as an integer if the string representation contains a dot, as float otherwise. This is expected behavior as far as I can see.

I recommend casting it after calling getCalculatedValue when you know that you got a date and time and you need a float.

[orcinus]

Exactly this. But, if it's a datetime field, it should always be (re)cast into a float, regardless of whether the representation ended up being an integer (by accident) or a float. Because an excel datetime is, by definition, internally a float.