Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Unsupported operand types: string + string in date format (PHP 8.x)
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?
detect returned string when calling fromString then skip operation
(line 132)  $value = DateTimeExcel\DateValue::fromString($value) + DateTimeExcel\TimeValue::fromString($value);


### What is the current behavior?
string+string is an issue in PHP 8.x
Fatal error: Uncaught TypeError: Unsupported operand types: string + string in C:\Program Files\EasyPHP-Devserver-17\eds-www\aplitec\includes\modules\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\TextData\Format.php:132
### What are the steps to reproduce?

<?php
require __DIR__ . '/vendor/autoload.php';
	$excelfile = 'BUG_PHPSpreadsheet_PHP8_DATE.xlsx';
  	$showPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($excelfile);
	
	$liassewriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($showPHPExcel, "Xlsx");
	$liassewriter->save("crash.xlsx");
?>
If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

### What features do you think are causing the issue

- [ ] Reader
- [ X] Writer
- [ ] Styles
- [ ] Data Validations
- [ ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?

DATE Cell formatting when date cell is still a keyword and not yet a valid date!

### Which versions of PhpSpreadsheet and PHP are affected? PHP >= 8.0
[BUG_PHPSpreadsheet_PHP8_DATE.xlsx](https://github.com/user-attachments/files/17961714/BUG_PHPSpreadsheet_PHP8_DATE.xlsx)


Pull request:
Fix TEXT and TIMEVALUE Functions 3.9 Branch
Fix #4249. Technically speaking, only the 1.29 branch needs fixing, and only for TEXT. It was fixed for the other branches by PR #3898. However, in adding test cases for the fix, it became apparent that PhpSpreadsheet's parsing in TIMEVALUE (which is called from TEXT in the original issue) did not really match Excel's. There are probably still edge cases where it doesn't, but, in the absence of a spec for how it operates, this will do for now.

We do not usually backport fixes from the master branch. Because this is more of a forward port from the earlier branch, there is an equivalent PR for each active branch.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [x] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [x] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 886edd3bf..09a12c1d0 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,12 @@ All notable changes to this project will be documented in this file.
 The format is based on [Keep a Changelog](https://keepachangelog.com)
 and this project adheres to [Semantic Versioning](https://semver.org).
 
+# TBD - 3.9.2
+
+### Fixed
+
+- TEXT and TIMEVALUE functions. [Issue #4249](https://github.com/PHPOffice/PhpSpreadsheet/issues/4249) [PR #4355](https://github.com/PHPOffice/PhpSpreadsheet/pull/4355)
+
 ## 2025-02-07 - 3.9.1
 
 ### Fixed
diff --git a/src/PhpSpreadsheet/Calculation/DateTimeExcel/TimeValue.php b/src/PhpSpreadsheet/Calculation/DateTimeExcel/TimeValue.php
index d8c53b476..dceb327a4 100644
--- a/src/PhpSpreadsheet/Calculation/DateTimeExcel/TimeValue.php
+++ b/src/PhpSpreadsheet/Calculation/DateTimeExcel/TimeValue.php
@@ -2,6 +2,7 @@
 
 namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
 
+use Composer\Pcre\Preg;
 use Datetime;
 use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
 use PhpOffice\PhpSpreadsheet\Calculation\Functions;
@@ -12,6 +13,19 @@ class TimeValue
 {
     use ArrayEnabled;
 
+    private const EXTRACT_TIME = '/\b'
+        . '(\d+)' // match[1] - hour
+        . '(:' // start of match[2] (rest of string) - colon
+        . '(\d+' // start of match[3] - minute
+        . '(:\d+' // start of match[4] - colon and seconds
+        . '([.]\d+)?' // match[5] - optional decimal point followed by fractional seconds
+        . ')?' // end of match[4], which is optional
+        . ')' // end of match 3
+        // Excel does not require 'm' to trail 'a' or 'p'; Php does
+        . '(\s*(a|p))?' // match[6] optional whitespace followed by optional match[7] a or p
+        . ')' // end of match[2]
+        . '/i';
+
     /**
      * TIMEVALUE.
      *
@@ -43,17 +57,20 @@ class TimeValue
         }
 
         // try to parse as time iff there is at least one digit
-        if (is_string($timeValue) && preg_match('/\\d/', $timeValue) !== 1) {
+        if (is_string($timeValue) && !Preg::isMatch('/\\d/', $timeValue)) {
             return ExcelError::VALUE();
         }
 
         $timeValue = trim((string) $timeValue, '"');
-        $timeValue = str_replace(['/', '.'], '-', $timeValue);
-
-        $arraySplit = preg_split('/[\/:\-\s]/', $timeValue) ?: [];
-        if ((count($arraySplit) == 2 || count($arraySplit) == 3) && $arraySplit[0] > 24) {
-            $arraySplit[0] = ((int) $arraySplit[0] % 24);
-            $timeValue = implode(':', $arraySplit);
+        if (Preg::isMatch(self::EXTRACT_TIME, $timeValue, $matches)) {
+            if (empty($matches[6])) { // am/pm
+                $hour = (int) $matches[0];
+                $timeValue = ($hour % 24) . $matches[2];
+            } elseif ($matches[6] === $matches[7]) { // Excel wants space before am/pm
+                return ExcelError::VALUE();
+            } else {
+                $timeValue = $matches[0] . 'm';
+            }
         }
 
         $PHPDateArray = Helpers::dateParse($timeValue);
diff --git a/src/PhpSpreadsheet/Calculation/TextData/Format.php b/src/PhpSpreadsheet/Calculation/TextData/Format.php
index 0560b376b..72514179c 100644
--- a/src/PhpSpreadsheet/Calculation/TextData/Format.php
+++ b/src/PhpSpreadsheet/Calculation/TextData/Format.php
@@ -2,6 +2,7 @@
 
 namespace PhpOffice\PhpSpreadsheet\Calculation\TextData;
 
+use Composer\Pcre\Preg;
 use DateTimeInterface;
 use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
 use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
@@ -133,11 +134,11 @@ class Format
 
         $format = (string) NumberFormat::convertSystemFormats($format);
 
-        if (!is_numeric($value) && Date::isDateTimeFormatCode($format)) {
+        if (!is_numeric($value) && Date::isDateTimeFormatCode($format) && !Preg::isMatch('/^\s*\d+(\s+\d+)+\s*$/', $value)) {
             $value1 = DateTimeExcel\DateValue::fromString($value);
             $value2 = DateTimeExcel\TimeValue::fromString($value);
             /** @var float|int|string */
-            $value = (is_numeric($value1) && is_numeric($value2)) ? ($value1 + $value2) : (is_numeric($value1) ? $value2 : $value1);
+            $value = (is_numeric($value1) && is_numeric($value2)) ? ($value1 + $value2) : (is_numeric($value1) ? $value1 : (is_numeric($value2) ? $value2 : $value));
         }
 
         return (string) NumberFormat::toFormattedString($value, $format);
@@ -293,7 +294,7 @@ class Format
         }
 
         if (!is_numeric($value)) {
-            $decimalPositions = preg_match_all('/' . preg_quote($decimalSeparator, '/') . '/', $value, $matches, PREG_OFFSET_CAPTURE);
+            $decimalPositions = Preg::matchAllWithOffsets('/' . preg_quote($decimalSeparator, '/') . '/', $value, $matches);
             if ($decimalPositions > 1) {
                 return ExcelError::VALUE();
             }


Test patch:
diff --git a/tests/data/Calculation/TextData/TEXT.php b/tests/data/Calculation/TextData/TEXT.php
index bf6474a28..1eee82814 100644
--- a/tests/data/Calculation/TextData/TEXT.php
+++ b/tests/data/Calculation/TextData/TEXT.php
@@ -73,6 +73,56 @@ return [
         '2014-02-15 16:17',
         'dd-mmm-yyyy HH:MM:SS AM/PM',
     ],
+    'datetime integer' => [
+        '1900-01-06 00:00',
+        6,
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime integer as string' => [
+        '1900-01-06 00:00',
+        '6',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime 2 integers without date delimiters' => [
+        '5 6',
+        '5 6',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime 2 integers separated by hyphen' => [
+        (new DateTimeImmutable())->format('Y') . '-05-13 00:00',
+        '5-13',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime string date only' => [
+        '1951-01-23 00:00',
+        'January 23, 1951',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime string time followed by date' => [
+        '1952-05-02 03:54',
+        '3:54 May 2, 1952',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime string date followed by time pm' => [
+        '1952-05-02 15:54',
+        'May 2, 1952 3:54 pm',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime string date followed by time p' => [
+        '1952-05-02 15:54',
+        'May 2, 1952 3:54 p',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime decimal string interpreted as time' => [
+        '1900-01-02 12:00',
+        '2.5',
+        'yyyy-mm-dd hh:mm',
+    ],
+    'datetime unparseable string' => [
+        'xyz',
+        'xyz',
+        'yyyy-mm-dd hh:mm',
+    ],
     [
         '1 3/4',
         1.75,