[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   4  
   5  use DateTimeImmutable;
   6  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;
   9  
  10  class DateValue
  11  {
  12      use ArrayEnabled;
  13  
  14      /**
  15       * DATEVALUE.
  16       *
  17       * Returns a value that represents a particular date.
  18       * Use DATEVALUE to convert a date represented by a text string to an Excel or PHP date/time stamp
  19       * value.
  20       *
  21       * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date
  22       * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way.
  23       *
  24       * Excel Function:
  25       *        DATEVALUE(dateValue)
  26       *
  27       * @param array|string $dateValue Text that represents a date in a Microsoft Excel date format.
  28       *                                    For example, "1/30/2008" or "30-Jan-2008" are text strings within
  29       *                                    quotation marks that represent dates. Using the default date
  30       *                                    system in Excel for Windows, date_text must represent a date from
  31       *                                    January 1, 1900, to December 31, 9999. Using the default date
  32       *                                    system in Excel for the Macintosh, date_text must represent a date
  33       *                                    from January 1, 1904, to December 31, 9999. DATEVALUE returns the
  34       *                                    #VALUE! error value if date_text is out of this range.
  35       *                         Or can be an array of date values
  36       *
  37       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  38       *                        depending on the value of the ReturnDateType flag
  39       *         If an array of numbers is passed as the argument, then the returned result will also be an array
  40       *            with the same dimensions
  41       */
  42      public static function fromString($dateValue)
  43      {
  44          if (is_array($dateValue)) {
  45              return self::evaluateSingleArgumentArray([self::class, __FUNCTION__], $dateValue);
  46          }
  47  
  48          $dti = new DateTimeImmutable();
  49          $baseYear = SharedDateHelper::getExcelCalendar();
  50          $dateValue = trim($dateValue ?? '', '"');
  51          //    Strip any ordinals because they're allowed in Excel (English only)
  52          $dateValue = (string) preg_replace('/(\d)(st|nd|rd|th)([ -\/])/Ui', '$1$3', $dateValue);
  53          //    Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany)
  54          $dateValue = str_replace(['/', '.', '-', '  '], ' ', $dateValue);
  55  
  56          $yearFound = false;
  57          $t1 = explode(' ', $dateValue);
  58          $t = '';
  59          foreach ($t1 as &$t) {
  60              if ((is_numeric($t)) && ($t > 31)) {
  61                  if ($yearFound) {
  62                      return ExcelError::VALUE();
  63                  }
  64                  if ($t < 100) {
  65                      $t += 1900;
  66                  }
  67                  $yearFound = true;
  68              }
  69          }
  70          if (count($t1) === 1) {
  71              //    We've been fed a time value without any date
  72              return ((strpos((string) $t, ':') === false)) ? ExcelError::Value() : 0.0;
  73          }
  74          unset($t);
  75  
  76          $dateValue = self::t1ToString($t1, $dti, $yearFound);
  77  
  78          $PHPDateArray = self::setUpArray($dateValue, $dti);
  79  
  80          return self::finalResults($PHPDateArray, $dti, $baseYear);
  81      }
  82  
  83      private static function t1ToString(array $t1, DateTimeImmutable $dti, bool $yearFound): string
  84      {
  85          if (count($t1) == 2) {
  86              //    We only have two parts of the date: either day/month or month/year
  87              if ($yearFound) {
  88                  array_unshift($t1, 1);
  89              } else {
  90                  if (is_numeric($t1[1]) && $t1[1] > 29) {
  91                      $t1[1] += 1900;
  92                      array_unshift($t1, 1);
  93                  } else {
  94                      $t1[] = $dti->format('Y');
  95                  }
  96              }
  97          }
  98          $dateValue = implode(' ', $t1);
  99  
 100          return $dateValue;
 101      }
 102  
 103      /**
 104       * Parse date.
 105       */
 106      private static function setUpArray(string $dateValue, DateTimeImmutable $dti): array
 107      {
 108          $PHPDateArray = Helpers::dateParse($dateValue);
 109          if (!Helpers::dateParseSucceeded($PHPDateArray)) {
 110              // If original count was 1, we've already returned.
 111              // If it was 2, we added another.
 112              // Therefore, neither of the first 2 stroks below can fail.
 113              $testVal1 = strtok($dateValue, '- ');
 114              $testVal2 = strtok('- ');
 115              $testVal3 = strtok('- ') ?: $dti->format('Y');
 116              Helpers::adjustYear((string) $testVal1, (string) $testVal2, $testVal3);
 117              $PHPDateArray = Helpers::dateParse($testVal1 . '-' . $testVal2 . '-' . $testVal3);
 118              if (!Helpers::dateParseSucceeded($PHPDateArray)) {
 119                  $PHPDateArray = Helpers::dateParse($testVal2 . '-' . $testVal1 . '-' . $testVal3);
 120              }
 121          }
 122  
 123          return $PHPDateArray;
 124      }
 125  
 126      /**
 127       * Final results.
 128       *
 129       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
 130       *                        depending on the value of the ReturnDateType flag
 131       */
 132      private static function finalResults(array $PHPDateArray, DateTimeImmutable $dti, int $baseYear)
 133      {
 134          $retValue = ExcelError::Value();
 135          if (Helpers::dateParseSucceeded($PHPDateArray)) {
 136              // Execute function
 137              Helpers::replaceIfEmpty($PHPDateArray['year'], $dti->format('Y'));
 138              if ($PHPDateArray['year'] < $baseYear) {
 139                  return ExcelError::VALUE();
 140              }
 141              Helpers::replaceIfEmpty($PHPDateArray['month'], $dti->format('m'));
 142              Helpers::replaceIfEmpty($PHPDateArray['day'], $dti->format('d'));
 143              $PHPDateArray['hour'] = 0;
 144              $PHPDateArray['minute'] = 0;
 145              $PHPDateArray['second'] = 0;
 146              $month = (int) $PHPDateArray['month'];
 147              $day = (int) $PHPDateArray['day'];
 148              $year = (int) $PHPDateArray['year'];
 149              if (!checkdate($month, $day, $year)) {
 150                  return ($year === 1900 && $month === 2 && $day === 29) ? Helpers::returnIn3FormatsFloat(60.0) : ExcelError::VALUE();
 151              }
 152              $retValue = Helpers::returnIn3FormatsArray($PHPDateArray, true);
 153          }
 154  
 155          return $retValue;
 156      }
 157  }