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