Dates read in wrong format despite user input; no option to ignore only date formatting

[LaraGb]

Users enter dates in format (DD/MM/YYYY), but when reading with PhpSpreadsheet, dates come out in American format (MM/DD/YYYY). Using setReadDataOnly(true) disables all formatting, which isn’t ideal because I want to keep other formats intact. There’s no option to ignore only date formatting. The XLS is dynamic, so I can’t specify columns manually.

Expected:
Ability to read dates respecting user input format or at least an option to ignore only date formatting without losing other styles.

[oleibman]

Dates are stored in Excel as numbers, not as strings. So ... how did the user enter the data into the spreadsheet? Was any styling (especially number formatting) applied? Can you upload a spreadsheet which demonstrates your problem?

For the record, I strongly recommend the use of an unambiguous format (e.g. yyyy-mm-dd) rather than ambiguous formats such as you mention above. You will avoid lots of problems if you do.

[LaraGb]

When a user types just the number 5 in a date column, instead of showing exactly what they typed, the system automatically converts it into a valid date like "05/01/1900".
This is a problem because I need to know exactly what the user entered for validation, not a date that was automatically formatted.

If the user types only "5", the date validation should fail and a warning should prompt them to enter a correct value.
But since the system automatically converts the input into a valid date, I cannot accurately validate what the user originally typed.

To fix this, I changed the column format to text because users download a template to fill out.
However, some users copy and paste from other XLS where the cells are already formatted as dates, so even though I set the column as text in the template, still interprets and keeps the value as a valid date.

So the problem remains: when using the date type, I cannot capture exactly what was typed, which makes proper validation difficult.

I cannot ask the user to always format the field as text, because that would create a HORRIBLE user experience.

`function xls_read($FILE, $LAST_COLUMN, $DATE_COLUMNS = array(), $DATE_FORMAT = 'ISO') {

$XLS = \PhpOffice\PhpSpreadsheet\IOFactory::load($FILE);
$MAX_ROW = $XLS->getActiveSheet()->getHighestDataRow();

// Use DD/MM/YYYY for 'UK' format, else ISO YYYY-MM-DD
$DATE_FORMAT_CODE = ($DATE_FORMAT == 'UK') ? 'DD/MM/YYYY' : 'YYYY-MM-DD';

foreach ($DATE_COLUMNS as $col) {
    $XLS->getActiveSheet()->getStyle($col . '1:' . $col . $MAX_ROW)->getNumberFormat()->setFormatCode($DATE_FORMAT_CODE);
}

return $XLS->getActiveSheet()->rangeToArray(
    'A2:' . $LAST_COLUMN . $MAX_ROW,   // The worksheet range we want to retrieve
    NULL,   // Value to return for empty cells
    TRUE,   // Should formulas be calculated (like getCalculatedValue() for each cell)
    TRUE,   // Should values be formatted (like getFormattedValue() for each cell)
    TRUE    // Should the returned array be indexed by cell row and column
);

}
`

The best scenario would be if it read dates as text too, because that way I could validate exactly what the user typed without getting a false date like when they type "05" and it becomes "05/01/1900".

[oleibman]

I'm sorry, I still don't understand your problem, in particular the part about Excel converting a number to a date on its own. Can you please upload a spreadsheet which behaves in the manner you describe?

[Jim537]

When a user types just the number 5 in a date column, instead of showing exactly what they typed, the system automatically converts it into a valid date like "05/01/1900"

Well this is interesting - With 5 I get "04/01/1900" in LibreOffice Calc but when I load the attached file into phpspreadsheet it gives me "05/01/1900".

If you have 5 in cell A2 (formatted as a number)
then put '=A2' into cell B2 (formatted as a date) you get "04/01/1900" (LibreOffice Calc)
then put '=B2' into cell C2 (formatted as a number) you get back to 5 again.

You could use this to validate the entry - maybe in a hidden column.

The other way using this code returns 5 for all cells in the attached file.

$dataArray = $spreadsheet->getActiveSheet()
    ->rangeToArray(
        'A1:D3', 
        NULL,        // Value to be returned for empty cells
        TRUE,        // Formulas to be calculated
        FALSE,       // values to be formatted << this is the important setting
        TRUE         // array indexed by cell row and cell column
    );

Test.xlsx

[oleibman]

Your problem with dates before March 1, 1900 is due to a really dumb problem. Lotus 1-2-3 decided that Feb. 29, 1900 was a real date. Microsoft Excel, which came later, wanted complete compatibility with Lotus. Without going into too much detail, that's why Excel behaves as it does. OpenOffice decided to go with reality; that's why it behaves as it does. If dates that early are not of concern to you, you might consider using 1904-based dates instead.

[oleibman]

As for the rest of your problem, it is due to the way Excel works, not PhpSpreadsheet. You have set up column B so that all cells in it use format dd-mm-yyyy. Excel stores all date-time values as an integer (representing the number of days from the base data) and a fraction (representing the time of day). What it does not do is store the data as the user input it into the spreadsheet. Whether you enter the data as 5 or 1900-01-05 or Jan 5, 1900, Excel will just store a value of 5 in the cell, and will then format that to your specification. There is nothing you, or we, can do to retrieve the user's original input.

It is possible that you could use Data Validation to validate the field as a date, say, greater than 1904-01-01. This reduces the opportunity for the user to enter 5 and have Excel think it is valid. However, it will still accept integers that don't look like dates (e.g. 1461), and, as above, there is nothing you can do about that.