DateValue.php

Go to the documentation of this file.

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;

use DateTimeImmutable;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;

class DateValue
{
    public static function fromString($dateValue)
    {
        $dti = new DateTimeImmutable();
        $baseYear = SharedDateHelper::getExcelCalendar();
        $dateValue = trim(Functions::flattenSingleValue($dateValue), '"');
        //    Strip any ordinals because they're allowed in Excel (English only)
        $dateValue = preg_replace('/(\d)(st|nd|rd|th)([ -\/])/Ui', '$1$3', $dateValue) ?? '';
        //    Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany)
        $dateValue = str_replace(['/', '.', '-', '  '], ' ', $dateValue);

        $yearFound = false;
        $t1 = explode(' ', $dateValue);
        $t = '';
        foreach ($t1 as &$t) {
            if ((is_numeric($t)) && ($t > 31)) {
                if ($yearFound) {
                    return Functions::VALUE();
                }
                if ($t < 100) {
                    $t += 1900;
                }
                $yearFound = true;
            }
        }
        if (count($t1) === 1) {
            //    We've been fed a time value without any date
            return ((strpos((string) $t, ':') === false)) ? Functions::Value() : 0.0;
        }
        unset($t);

        $dateValue = self::t1ToString($t1, $dti, $yearFound);

        $PHPDateArray = self::setUpArray($dateValue, $dti);

        return self::finalResults($PHPDateArray, $dti, $baseYear);
    }

    private static function t1ToString(array $t1, DateTimeImmutable $dti, bool $yearFound): string
    {
        if (count($t1) == 2) {
            //    We only have two parts of the date: either day/month or month/year
            if ($yearFound) {
                array_unshift($t1, 1);
            } else {
                if (is_numeric($t1[1]) && $t1[1] > 29) {
                    $t1[1] += 1900;
                    array_unshift($t1, 1);
                } else {
                    $t1[] = $dti->format('Y');
                }
            }
        }
        $dateValue = implode(' ', $t1);

        return $dateValue;
    }

    private static function setUpArray(string $dateValue, DateTimeImmutable $dti)
    {
        $PHPDateArray = date_parse($dateValue);
        if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) {
            // If original count was 1, we've already returned.
            // If it was 2, we added another.
            // Therefore, neither of the first 2 stroks below can fail.
            $testVal1 = strtok($dateValue, '- ');
            $testVal2 = strtok('- ');
            $testVal3 = strtok('- ') ?: $dti->format('Y');
            Helpers::adjustYear((string) $testVal1, (string) $testVal2, $testVal3);
            $PHPDateArray = date_parse($testVal1 . '-' . $testVal2 . '-' . $testVal3);
            if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) {
                $PHPDateArray = date_parse($testVal2 . '-' . $testVal1 . '-' . $testVal3);
            }
        }

        return $PHPDateArray;
    }

    private static function finalResults($PHPDateArray, DateTimeImmutable $dti, int $baseYear)
    {
        $retValue = Functions::Value();
        if (is_array($PHPDateArray) && $PHPDateArray['error_count'] == 0) {
            // Execute function
            Helpers::replaceIfEmpty($PHPDateArray['year'], $dti->format('Y'));
            if ($PHPDateArray['year'] < $baseYear) {
                return Functions::VALUE();
            }
            Helpers::replaceIfEmpty($PHPDateArray['month'], $dti->format('m'));
            Helpers::replaceIfEmpty($PHPDateArray['day'], $dti->format('d'));
            $PHPDateArray['hour'] = 0;
            $PHPDateArray['minute'] = 0;
            $PHPDateArray['second'] = 0;
            $month = (int) $PHPDateArray['month'];
            $day = (int) $PHPDateArray['day'];
            $year = (int) $PHPDateArray['year'];
            if (!checkdate($month, $day, $year)) {
                return ($year === 1900 && $month === 2 && $day === 29) ? Helpers::returnIn3FormatsFloat(60.0) : Functions::VALUE();
            }
            $retValue = Helpers::returnIn3FormatsArray($PHPDateArray, true);
        }

        return $retValue;
    }
}