[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 PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;
   9  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  10  
  11  class Date
  12  {
  13      use ArrayEnabled;
  14  
  15      /**
  16       * DATE.
  17       *
  18       * The DATE function returns a value that represents a particular date.
  19       *
  20       * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date
  21       * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way.
  22       *
  23       * Excel Function:
  24       *        DATE(year,month,day)
  25       *
  26       * PhpSpreadsheet is a lot more forgiving than MS Excel when passing non numeric values to this function.
  27       * A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted,
  28       *     as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language.
  29       *
  30       * @param array|int $year The value of the year argument can include one to four digits.
  31       *                                Excel interprets the year argument according to the configured
  32       *                                date system: 1900 or 1904.
  33       *                                If year is between 0 (zero) and 1899 (inclusive), Excel adds that
  34       *                                value to 1900 to calculate the year. For example, DATE(108,1,2)
  35       *                                returns January 2, 2008 (1900+108).
  36       *                                If year is between 1900 and 9999 (inclusive), Excel uses that
  37       *                                value as the year. For example, DATE(2008,1,2) returns January 2,
  38       *                                2008.
  39       *                                If year is less than 0 or is 10000 or greater, Excel returns the
  40       *                                #NUM! error value.
  41       * @param array|int $month A positive or negative integer representing the month of the year
  42       *                                from 1 to 12 (January to December).
  43       *                                If month is greater than 12, month adds that number of months to
  44       *                                the first month in the year specified. For example, DATE(2008,14,2)
  45       *                                returns the serial number representing February 2, 2009.
  46       *                                If month is less than 1, month subtracts the magnitude of that
  47       *                                number of months, plus 1, from the first month in the year
  48       *                                specified. For example, DATE(2008,-3,2) returns the serial number
  49       *                                representing September 2, 2007.
  50       * @param array|int $day A positive or negative integer representing the day of the month
  51       *                                from 1 to 31.
  52       *                                If day is greater than the number of days in the month specified,
  53       *                                day adds that number of days to the first day in the month. For
  54       *                                example, DATE(2008,1,35) returns the serial number representing
  55       *                                February 4, 2008.
  56       *                                If day is less than 1, day subtracts the magnitude that number of
  57       *                                days, plus one, from the first day of the month specified. For
  58       *                                example, DATE(2008,1,-15) returns the serial number representing
  59       *                                December 16, 2007.
  60       *
  61       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  62       *                        depending on the value of the ReturnDateType flag
  63       *         If an array of numbers is passed as the argument, then the returned result will also be an array
  64       *            with the same dimensions
  65       */
  66      public static function fromYMD($year, $month, $day)
  67      {
  68          if (is_array($year) || is_array($month) || is_array($day)) {
  69              return self::evaluateArrayArguments([self::class, __FUNCTION__], $year, $month, $day);
  70          }
  71  
  72          $baseYear = SharedDateHelper::getExcelCalendar();
  73  
  74          try {
  75              $year = self::getYear($year, $baseYear);
  76              $month = self::getMonth($month);
  77              $day = self::getDay($day);
  78              self::adjustYearMonth($year, $month, $baseYear);
  79          } catch (Exception $e) {
  80              return $e->getMessage();
  81          }
  82  
  83          // Execute function
  84          $excelDateValue = SharedDateHelper::formattedPHPToExcel($year, $month, $day);
  85  
  86          return Helpers::returnIn3FormatsFloat($excelDateValue);
  87      }
  88  
  89      /**
  90       * Convert year from multiple formats to int.
  91       *
  92       * @param mixed $year
  93       */
  94      private static function getYear($year, int $baseYear): int
  95      {
  96          $year = ($year !== null) ? StringHelper::testStringAsNumeric((string) $year) : 0;
  97          if (!is_numeric($year)) {
  98              throw new Exception(ExcelError::VALUE());
  99          }
 100          $year = (int) $year;
 101  
 102          if ($year < ($baseYear - 1900)) {
 103              throw new Exception(ExcelError::NAN());
 104          }
 105          if ((($baseYear - 1900) !== 0) && ($year < $baseYear) && ($year >= 1900)) {
 106              throw new Exception(ExcelError::NAN());
 107          }
 108  
 109          if (($year < $baseYear) && ($year >= ($baseYear - 1900))) {
 110              $year += 1900;
 111          }
 112  
 113          return (int) $year;
 114      }
 115  
 116      /**
 117       * Convert month from multiple formats to int.
 118       *
 119       * @param mixed $month
 120       */
 121      private static function getMonth($month): int
 122      {
 123          if (($month !== null) && (!is_numeric($month))) {
 124              $month = SharedDateHelper::monthStringToNumber($month);
 125          }
 126  
 127          $month = ($month !== null) ? StringHelper::testStringAsNumeric((string) $month) : 0;
 128          if (!is_numeric($month)) {
 129              throw new Exception(ExcelError::VALUE());
 130          }
 131  
 132          return (int) $month;
 133      }
 134  
 135      /**
 136       * Convert day from multiple formats to int.
 137       *
 138       * @param mixed $day
 139       */
 140      private static function getDay($day): int
 141      {
 142          if (($day !== null) && (!is_numeric($day))) {
 143              $day = SharedDateHelper::dayStringToNumber($day);
 144          }
 145  
 146          $day = ($day !== null) ? StringHelper::testStringAsNumeric((string) $day) : 0;
 147          if (!is_numeric($day)) {
 148              throw new Exception(ExcelError::VALUE());
 149          }
 150  
 151          return (int) $day;
 152      }
 153  
 154      private static function adjustYearMonth(int &$year, int &$month, int $baseYear): void
 155      {
 156          if ($month < 1) {
 157              //    Handle year/month adjustment if month < 1
 158              --$month;
 159              $year += ceil($month / 12) - 1;
 160              $month = 13 - abs($month % 12);
 161          } elseif ($month > 12) {
 162              //    Handle year/month adjustment if month > 12
 163              $year += floor($month / 12);
 164              $month = ($month % 12);
 165          }
 166  
 167          // Re-validate the year parameter after adjustments
 168          if (($year < $baseYear) || ($year >= 10000)) {
 169              throw new Exception(ExcelError::NAN());
 170          }
 171      }
 172  }