Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
- Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
- PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5 use DateTimeImmutable; 6 use DateTimeInterface; 7 use PhpOffice\PhpSpreadsheet\Shared\Date; 8 use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 9 10 class DateTime 11 { 12 /** 13 * Identify if a year is a leap year or not. 14 * 15 * @param int|string $year The year to test 16 * 17 * @return bool TRUE if the year is a leap year, otherwise FALSE 18 */ 19 public static function isLeapYear($year) 20 { 21 return (($year % 4) === 0) && (($year % 100) !== 0) || (($year % 400) === 0); 22 } 23 24 /** 25 * Return the number of days between two dates based on a 360 day calendar. 26 * 27 * @param int $startDay Day of month of the start date 28 * @param int $startMonth Month of the start date 29 * @param int $startYear Year of the start date 30 * @param int $endDay Day of month of the start date 31 * @param int $endMonth Month of the start date 32 * @param int $endYear Year of the start date 33 * @param bool $methodUS Whether to use the US method or the European method of calculation 34 * 35 * @return int Number of days between the start date and the end date 36 */ 37 private static function dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, $methodUS) 38 { 39 if ($startDay == 31) { 40 --$startDay; 41 } elseif ($methodUS && ($startMonth == 2 && ($startDay == 29 || ($startDay == 28 && !self::isLeapYear($startYear))))) { 42 $startDay = 30; 43 } 44 if ($endDay == 31) { 45 if ($methodUS && $startDay != 30) { 46 $endDay = 1; 47 if ($endMonth == 12) { 48 ++$endYear; 49 $endMonth = 1; 50 } else { 51 ++$endMonth; 52 } 53 } else { 54 $endDay = 30; 55 } 56 } 57 58 return $endDay + $endMonth * 30 + $endYear * 360 - $startDay - $startMonth * 30 - $startYear * 360; 59 } 60 61 /** 62 * getDateValue. 63 * 64 * @param mixed $dateValue 65 * 66 * @return mixed Excel date/time serial value, or string if error 67 */ 68 public static function getDateValue($dateValue) 69 { 70 if (!is_numeric($dateValue)) { 71 if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) { 72 $dateValue = Date::PHPToExcel($dateValue); 73 } else { 74 $saveReturnDateType = Functions::getReturnDateType(); 75 Functions::setReturnDateType(Functions::RETURNDATE_EXCEL); 76 $dateValue = self::DATEVALUE($dateValue); 77 Functions::setReturnDateType($saveReturnDateType); 78 } 79 } 80 81 return $dateValue; 82 } 83 84 /** 85 * getTimeValue. 86 * 87 * @param string $timeValue 88 * 89 * @return mixed Excel date/time serial value, or string if error 90 */ 91 private static function getTimeValue($timeValue) 92 { 93 $saveReturnDateType = Functions::getReturnDateType(); 94 Functions::setReturnDateType(Functions::RETURNDATE_EXCEL); 95 $timeValue = self::TIMEVALUE($timeValue); 96 Functions::setReturnDateType($saveReturnDateType); 97 98 return $timeValue; 99 } 100 101 private static function adjustDateByMonths($dateValue = 0, $adjustmentMonths = 0) 102 { 103 // Execute function 104 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 105 $oMonth = (int) $PHPDateObject->format('m'); 106 $oYear = (int) $PHPDateObject->format('Y'); 107 108 $adjustmentMonthsString = (string) $adjustmentMonths; 109 if ($adjustmentMonths > 0) { 110 $adjustmentMonthsString = '+' . $adjustmentMonths; 111 } 112 if ($adjustmentMonths != 0) { 113 $PHPDateObject->modify($adjustmentMonthsString . ' months'); 114 } 115 $nMonth = (int) $PHPDateObject->format('m'); 116 $nYear = (int) $PHPDateObject->format('Y'); 117 118 $monthDiff = ($nMonth - $oMonth) + (($nYear - $oYear) * 12); 119 if ($monthDiff != $adjustmentMonths) { 120 $adjustDays = (int) $PHPDateObject->format('d'); 121 $adjustDaysString = '-' . $adjustDays . ' days'; 122 $PHPDateObject->modify($adjustDaysString); 123 } 124 125 return $PHPDateObject; 126 } 127 128 /** 129 * DATETIMENOW. 130 * 131 * Returns the current date and time. 132 * The NOW function is useful when you need to display the current date and time on a worksheet or 133 * calculate a value based on the current date and time, and have that value updated each time you 134 * open the worksheet. 135 * 136 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date 137 * and time format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 138 * 139 * Excel Function: 140 * NOW() 141 * 142 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 143 * depending on the value of the ReturnDateType flag 144 */ 145 public static function DATETIMENOW() 146 { 147 $saveTimeZone = date_default_timezone_get(); 148 date_default_timezone_set('UTC'); 149 $retValue = false; 150 switch (Functions::getReturnDateType()) { 151 case Functions::RETURNDATE_EXCEL: 152 $retValue = (float) Date::PHPToExcel(time()); 153 154 break; 155 case Functions::RETURNDATE_UNIX_TIMESTAMP: 156 $retValue = (int) time(); 157 158 break; 159 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 160 $retValue = new \DateTime(); 161 162 break; 163 } 164 date_default_timezone_set($saveTimeZone); 165 166 return $retValue; 167 } 168 169 /** 170 * DATENOW. 171 * 172 * Returns the current date. 173 * The NOW function is useful when you need to display the current date and time on a worksheet or 174 * calculate a value based on the current date and time, and have that value updated each time you 175 * open the worksheet. 176 * 177 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date 178 * and time format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 179 * 180 * Excel Function: 181 * TODAY() 182 * 183 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 184 * depending on the value of the ReturnDateType flag 185 */ 186 public static function DATENOW() 187 { 188 $saveTimeZone = date_default_timezone_get(); 189 date_default_timezone_set('UTC'); 190 $retValue = false; 191 $excelDateTime = floor(Date::PHPToExcel(time())); 192 switch (Functions::getReturnDateType()) { 193 case Functions::RETURNDATE_EXCEL: 194 $retValue = (float) $excelDateTime; 195 196 break; 197 case Functions::RETURNDATE_UNIX_TIMESTAMP: 198 $retValue = (int) Date::excelToTimestamp($excelDateTime); 199 200 break; 201 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 202 $retValue = Date::excelToDateTimeObject($excelDateTime); 203 204 break; 205 } 206 date_default_timezone_set($saveTimeZone); 207 208 return $retValue; 209 } 210 211 /** 212 * DATE. 213 * 214 * The DATE function returns a value that represents a particular date. 215 * 216 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date 217 * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 218 * 219 * Excel Function: 220 * DATE(year,month,day) 221 * 222 * PhpSpreadsheet is a lot more forgiving than MS Excel when passing non numeric values to this function. 223 * A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted, 224 * as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language. 225 * 226 * @param int $year The value of the year argument can include one to four digits. 227 * Excel interprets the year argument according to the configured 228 * date system: 1900 or 1904. 229 * If year is between 0 (zero) and 1899 (inclusive), Excel adds that 230 * value to 1900 to calculate the year. For example, DATE(108,1,2) 231 * returns January 2, 2008 (1900+108). 232 * If year is between 1900 and 9999 (inclusive), Excel uses that 233 * value as the year. For example, DATE(2008,1,2) returns January 2, 234 * 2008. 235 * If year is less than 0 or is 10000 or greater, Excel returns the 236 * #NUM! error value. 237 * @param int $month A positive or negative integer representing the month of the year 238 * from 1 to 12 (January to December). 239 * If month is greater than 12, month adds that number of months to 240 * the first month in the year specified. For example, DATE(2008,14,2) 241 * returns the serial number representing February 2, 2009. 242 * If month is less than 1, month subtracts the magnitude of that 243 * number of months, plus 1, from the first month in the year 244 * specified. For example, DATE(2008,-3,2) returns the serial number 245 * representing September 2, 2007. 246 * @param int $day A positive or negative integer representing the day of the month 247 * from 1 to 31. 248 * If day is greater than the number of days in the month specified, 249 * day adds that number of days to the first day in the month. For 250 * example, DATE(2008,1,35) returns the serial number representing 251 * February 4, 2008. 252 * If day is less than 1, day subtracts the magnitude that number of 253 * days, plus one, from the first day of the month specified. For 254 * example, DATE(2008,1,-15) returns the serial number representing 255 * December 16, 2007. 256 * 257 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 258 * depending on the value of the ReturnDateType flag 259 */ 260 public static function DATE($year = 0, $month = 1, $day = 1) 261 { 262 $year = Functions::flattenSingleValue($year); 263 $month = Functions::flattenSingleValue($month); 264 $day = Functions::flattenSingleValue($day); 265 266 if (($month !== null) && (!is_numeric($month))) { 267 $month = Date::monthStringToNumber($month); 268 } 269 270 if (($day !== null) && (!is_numeric($day))) { 271 $day = Date::dayStringToNumber($day); 272 } 273 274 $year = ($year !== null) ? StringHelper::testStringAsNumeric($year) : 0; 275 $month = ($month !== null) ? StringHelper::testStringAsNumeric($month) : 0; 276 $day = ($day !== null) ? StringHelper::testStringAsNumeric($day) : 0; 277 if ( 278 (!is_numeric($year)) || 279 (!is_numeric($month)) || 280 (!is_numeric($day)) 281 ) { 282 return Functions::VALUE(); 283 } 284 $year = (int) $year; 285 $month = (int) $month; 286 $day = (int) $day; 287 288 $baseYear = Date::getExcelCalendar(); 289 // Validate parameters 290 if ($year < ($baseYear - 1900)) { 291 return Functions::NAN(); 292 } 293 if ((($baseYear - 1900) != 0) && ($year < $baseYear) && ($year >= 1900)) { 294 return Functions::NAN(); 295 } 296 297 if (($year < $baseYear) && ($year >= ($baseYear - 1900))) { 298 $year += 1900; 299 } 300 301 if ($month < 1) { 302 // Handle year/month adjustment if month < 1 303 --$month; 304 $year += ceil($month / 12) - 1; 305 $month = 13 - abs($month % 12); 306 } elseif ($month > 12) { 307 // Handle year/month adjustment if month > 12 308 $year += floor($month / 12); 309 $month = ($month % 12); 310 } 311 312 // Re-validate the year parameter after adjustments 313 if (($year < $baseYear) || ($year >= 10000)) { 314 return Functions::NAN(); 315 } 316 317 // Execute function 318 $excelDateValue = Date::formattedPHPToExcel($year, $month, $day); 319 switch (Functions::getReturnDateType()) { 320 case Functions::RETURNDATE_EXCEL: 321 return (float) $excelDateValue; 322 case Functions::RETURNDATE_UNIX_TIMESTAMP: 323 return (int) Date::excelToTimestamp($excelDateValue); 324 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 325 return Date::excelToDateTimeObject($excelDateValue); 326 } 327 } 328 329 /** 330 * TIME. 331 * 332 * The TIME function returns a value that represents a particular time. 333 * 334 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time 335 * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 336 * 337 * Excel Function: 338 * TIME(hour,minute,second) 339 * 340 * @param int $hour A number from 0 (zero) to 32767 representing the hour. 341 * Any value greater than 23 will be divided by 24 and the remainder 342 * will be treated as the hour value. For example, TIME(27,0,0) = 343 * TIME(3,0,0) = .125 or 3:00 AM. 344 * @param int $minute A number from 0 to 32767 representing the minute. 345 * Any value greater than 59 will be converted to hours and minutes. 346 * For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM. 347 * @param int $second A number from 0 to 32767 representing the second. 348 * Any value greater than 59 will be converted to hours, minutes, 349 * and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 350 * or 12:33:20 AM 351 * 352 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 353 * depending on the value of the ReturnDateType flag 354 */ 355 public static function TIME($hour = 0, $minute = 0, $second = 0) 356 { 357 $hour = Functions::flattenSingleValue($hour); 358 $minute = Functions::flattenSingleValue($minute); 359 $second = Functions::flattenSingleValue($second); 360 361 if ($hour == '') { 362 $hour = 0; 363 } 364 if ($minute == '') { 365 $minute = 0; 366 } 367 if ($second == '') { 368 $second = 0; 369 } 370 371 if ((!is_numeric($hour)) || (!is_numeric($minute)) || (!is_numeric($second))) { 372 return Functions::VALUE(); 373 } 374 $hour = (int) $hour; 375 $minute = (int) $minute; 376 $second = (int) $second; 377 378 if ($second < 0) { 379 $minute += floor($second / 60); 380 $second = 60 - abs($second % 60); 381 if ($second == 60) { 382 $second = 0; 383 } 384 } elseif ($second >= 60) { 385 $minute += floor($second / 60); 386 $second = $second % 60; 387 } 388 if ($minute < 0) { 389 $hour += floor($minute / 60); 390 $minute = 60 - abs($minute % 60); 391 if ($minute == 60) { 392 $minute = 0; 393 } 394 } elseif ($minute >= 60) { 395 $hour += floor($minute / 60); 396 $minute = $minute % 60; 397 } 398 399 if ($hour > 23) { 400 $hour = $hour % 24; 401 } elseif ($hour < 0) { 402 return Functions::NAN(); 403 } 404 405 // Execute function 406 switch (Functions::getReturnDateType()) { 407 case Functions::RETURNDATE_EXCEL: 408 $date = 0; 409 $calendar = Date::getExcelCalendar(); 410 if ($calendar != Date::CALENDAR_WINDOWS_1900) { 411 $date = 1; 412 } 413 414 return (float) Date::formattedPHPToExcel($calendar, 1, $date, $hour, $minute, $second); 415 case Functions::RETURNDATE_UNIX_TIMESTAMP: 416 return (int) Date::excelToTimestamp(Date::formattedPHPToExcel(1970, 1, 1, $hour, $minute, $second)); // -2147468400; // -2147472000 + 3600 417 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 418 $dayAdjust = 0; 419 if ($hour < 0) { 420 $dayAdjust = floor($hour / 24); 421 $hour = 24 - abs($hour % 24); 422 if ($hour == 24) { 423 $hour = 0; 424 } 425 } elseif ($hour >= 24) { 426 $dayAdjust = floor($hour / 24); 427 $hour = $hour % 24; 428 } 429 $phpDateObject = new \DateTime('1900-01-01 ' . $hour . ':' . $minute . ':' . $second); 430 if ($dayAdjust != 0) { 431 $phpDateObject->modify($dayAdjust . ' days'); 432 } 433 434 return $phpDateObject; 435 } 436 } 437 438 /** 439 * DATEVALUE. 440 * 441 * Returns a value that represents a particular date. 442 * Use DATEVALUE to convert a date represented by a text string to an Excel or PHP date/time stamp 443 * value. 444 * 445 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date 446 * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 447 * 448 * Excel Function: 449 * DATEVALUE(dateValue) 450 * 451 * @param string $dateValue Text that represents a date in a Microsoft Excel date format. 452 * For example, "1/30/2008" or "30-Jan-2008" are text strings within 453 * quotation marks that represent dates. Using the default date 454 * system in Excel for Windows, date_text must represent a date from 455 * January 1, 1900, to December 31, 9999. Using the default date 456 * system in Excel for the Macintosh, date_text must represent a date 457 * from January 1, 1904, to December 31, 9999. DATEVALUE returns the 458 * #VALUE! error value if date_text is out of this range. 459 * 460 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 461 * depending on the value of the ReturnDateType flag 462 */ 463 public static function DATEVALUE($dateValue = 1) 464 { 465 $dateValue = trim(Functions::flattenSingleValue($dateValue), '"'); 466 // Strip any ordinals because they're allowed in Excel (English only) 467 $dateValue = preg_replace('/(\d)(st|nd|rd|th)([ -\/])/Ui', '$1$3', $dateValue); 468 // Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany) 469 $dateValue = str_replace(['/', '.', '-', ' '], ' ', $dateValue); 470 471 $yearFound = false; 472 $t1 = explode(' ', $dateValue); 473 foreach ($t1 as &$t) { 474 if ((is_numeric($t)) && ($t > 31)) { 475 if ($yearFound) { 476 return Functions::VALUE(); 477 } 478 if ($t < 100) { 479 $t += 1900; 480 } 481 $yearFound = true; 482 } 483 } 484 if ((count($t1) == 1) && (strpos($t, ':') !== false)) { 485 // We've been fed a time value without any date 486 return 0.0; 487 } elseif (count($t1) == 2) { 488 // We only have two parts of the date: either day/month or month/year 489 if ($yearFound) { 490 array_unshift($t1, 1); 491 } else { 492 if (is_numeric($t1[1]) && $t1[1] > 29) { 493 $t1[1] += 1900; 494 array_unshift($t1, 1); 495 } else { 496 $t1[] = date('Y'); 497 } 498 } 499 } 500 unset($t); 501 $dateValue = implode(' ', $t1); 502 503 $PHPDateArray = date_parse($dateValue); 504 if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { 505 $testVal1 = strtok($dateValue, '- '); 506 if ($testVal1 !== false) { 507 $testVal2 = strtok('- '); 508 if ($testVal2 !== false) { 509 $testVal3 = strtok('- '); 510 if ($testVal3 === false) { 511 $testVal3 = strftime('%Y'); 512 } 513 } else { 514 return Functions::VALUE(); 515 } 516 } else { 517 return Functions::VALUE(); 518 } 519 if ($testVal1 < 31 && $testVal2 < 12 && $testVal3 < 12 && strlen($testVal3) == 2) { 520 $testVal3 += 2000; 521 } 522 $PHPDateArray = date_parse($testVal1 . '-' . $testVal2 . '-' . $testVal3); 523 if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { 524 $PHPDateArray = date_parse($testVal2 . '-' . $testVal1 . '-' . $testVal3); 525 if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { 526 return Functions::VALUE(); 527 } 528 } 529 } 530 531 if (($PHPDateArray !== false) && ($PHPDateArray['error_count'] == 0)) { 532 // Execute function 533 if ($PHPDateArray['year'] == '') { 534 $PHPDateArray['year'] = strftime('%Y'); 535 } 536 if ($PHPDateArray['year'] < 1900) { 537 return Functions::VALUE(); 538 } 539 if ($PHPDateArray['month'] == '') { 540 $PHPDateArray['month'] = strftime('%m'); 541 } 542 if ($PHPDateArray['day'] == '') { 543 $PHPDateArray['day'] = strftime('%d'); 544 } 545 if (!checkdate($PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['year'])) { 546 return Functions::VALUE(); 547 } 548 $excelDateValue = floor( 549 Date::formattedPHPToExcel( 550 $PHPDateArray['year'], 551 $PHPDateArray['month'], 552 $PHPDateArray['day'], 553 $PHPDateArray['hour'], 554 $PHPDateArray['minute'], 555 $PHPDateArray['second'] 556 ) 557 ); 558 switch (Functions::getReturnDateType()) { 559 case Functions::RETURNDATE_EXCEL: 560 return (float) $excelDateValue; 561 case Functions::RETURNDATE_UNIX_TIMESTAMP: 562 return (int) Date::excelToTimestamp($excelDateValue); 563 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 564 return new \DateTime($PHPDateArray['year'] . '-' . $PHPDateArray['month'] . '-' . $PHPDateArray['day'] . ' 00:00:00'); 565 } 566 } 567 568 return Functions::VALUE(); 569 } 570 571 /** 572 * TIMEVALUE. 573 * 574 * Returns a value that represents a particular time. 575 * Use TIMEVALUE to convert a time represented by a text string to an Excel or PHP date/time stamp 576 * value. 577 * 578 * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time 579 * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. 580 * 581 * Excel Function: 582 * TIMEVALUE(timeValue) 583 * 584 * @param string $timeValue A text string that represents a time in any one of the Microsoft 585 * Excel time formats; for example, "6:45 PM" and "18:45" text strings 586 * within quotation marks that represent time. 587 * Date information in time_text is ignored. 588 * 589 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 590 * depending on the value of the ReturnDateType flag 591 */ 592 public static function TIMEVALUE($timeValue) 593 { 594 $timeValue = trim(Functions::flattenSingleValue($timeValue), '"'); 595 $timeValue = str_replace(['/', '.'], '-', $timeValue); 596 597 $arraySplit = preg_split('/[\/:\-\s]/', $timeValue); 598 if ((count($arraySplit) == 2 || count($arraySplit) == 3) && $arraySplit[0] > 24) { 599 $arraySplit[0] = ($arraySplit[0] % 24); 600 $timeValue = implode(':', $arraySplit); 601 } 602 603 $PHPDateArray = date_parse($timeValue); 604 if (($PHPDateArray !== false) && ($PHPDateArray['error_count'] == 0)) { 605 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 606 $excelDateValue = Date::formattedPHPToExcel( 607 $PHPDateArray['year'], 608 $PHPDateArray['month'], 609 $PHPDateArray['day'], 610 $PHPDateArray['hour'], 611 $PHPDateArray['minute'], 612 $PHPDateArray['second'] 613 ); 614 } else { 615 $excelDateValue = Date::formattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1; 616 } 617 618 switch (Functions::getReturnDateType()) { 619 case Functions::RETURNDATE_EXCEL: 620 return (float) $excelDateValue; 621 case Functions::RETURNDATE_UNIX_TIMESTAMP: 622 return (int) $phpDateValue = Date::excelToTimestamp($excelDateValue + 25569) - 3600; 623 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 624 return new \DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']); 625 } 626 } 627 628 return Functions::VALUE(); 629 } 630 631 /** 632 * DATEDIF. 633 * 634 * @param mixed $startDate Excel date serial value, PHP date/time stamp, PHP DateTime object 635 * or a standard date string 636 * @param mixed $endDate Excel date serial value, PHP date/time stamp, PHP DateTime object 637 * or a standard date string 638 * @param string $unit 639 * 640 * @return int|string Interval between the dates 641 */ 642 public static function DATEDIF($startDate = 0, $endDate = 0, $unit = 'D') 643 { 644 $startDate = Functions::flattenSingleValue($startDate); 645 $endDate = Functions::flattenSingleValue($endDate); 646 $unit = strtoupper(Functions::flattenSingleValue($unit)); 647 648 if (is_string($startDate = self::getDateValue($startDate))) { 649 return Functions::VALUE(); 650 } 651 if (is_string($endDate = self::getDateValue($endDate))) { 652 return Functions::VALUE(); 653 } 654 655 // Validate parameters 656 if ($startDate > $endDate) { 657 return Functions::NAN(); 658 } 659 660 // Execute function 661 $difference = $endDate - $startDate; 662 663 $PHPStartDateObject = Date::excelToDateTimeObject($startDate); 664 $startDays = $PHPStartDateObject->format('j'); 665 $startMonths = $PHPStartDateObject->format('n'); 666 $startYears = $PHPStartDateObject->format('Y'); 667 668 $PHPEndDateObject = Date::excelToDateTimeObject($endDate); 669 $endDays = $PHPEndDateObject->format('j'); 670 $endMonths = $PHPEndDateObject->format('n'); 671 $endYears = $PHPEndDateObject->format('Y'); 672 673 $PHPDiffDateObject = $PHPEndDateObject->diff($PHPStartDateObject); 674 675 switch ($unit) { 676 case 'D': 677 $retVal = (int) $difference; 678 679 break; 680 case 'M': 681 $retVal = (int) 12 * $PHPDiffDateObject->format('%y') + $PHPDiffDateObject->format('%m'); 682 683 break; 684 case 'Y': 685 $retVal = (int) $PHPDiffDateObject->format('%y'); 686 687 break; 688 case 'MD': 689 if ($endDays < $startDays) { 690 $retVal = $endDays; 691 $PHPEndDateObject->modify('-' . $endDays . ' days'); 692 $adjustDays = $PHPEndDateObject->format('j'); 693 $retVal += ($adjustDays - $startDays); 694 } else { 695 $retVal = (int) $PHPDiffDateObject->format('%d'); 696 } 697 698 break; 699 case 'YM': 700 $retVal = (int) $PHPDiffDateObject->format('%m'); 701 702 break; 703 case 'YD': 704 $retVal = (int) $difference; 705 if ($endYears > $startYears) { 706 $isLeapStartYear = $PHPStartDateObject->format('L'); 707 $wasLeapEndYear = $PHPEndDateObject->format('L'); 708 709 // Adjust end year to be as close as possible as start year 710 while ($PHPEndDateObject >= $PHPStartDateObject) { 711 $PHPEndDateObject->modify('-1 year'); 712 $endYears = $PHPEndDateObject->format('Y'); 713 } 714 $PHPEndDateObject->modify('+1 year'); 715 716 // Get the result 717 $retVal = $PHPEndDateObject->diff($PHPStartDateObject)->days; 718 719 // Adjust for leap years cases 720 $isLeapEndYear = $PHPEndDateObject->format('L'); 721 $limit = new \DateTime($PHPEndDateObject->format('Y-02-29')); 722 if (!$isLeapStartYear && !$wasLeapEndYear && $isLeapEndYear && $PHPEndDateObject >= $limit) { 723 --$retVal; 724 } 725 } 726 727 break; 728 default: 729 $retVal = Functions::VALUE(); 730 } 731 732 return $retVal; 733 } 734 735 /** 736 * DAYS. 737 * 738 * Returns the number of days between two dates 739 * 740 * Excel Function: 741 * DAYS(endDate, startDate) 742 * 743 * @param DateTimeImmutable|float|int|string $endDate Excel date serial value (float), 744 * PHP date timestamp (integer), PHP DateTime object, or a standard date string 745 * @param DateTimeImmutable|float|int|string $startDate Excel date serial value (float), 746 * PHP date timestamp (integer), PHP DateTime object, or a standard date string 747 * 748 * @return int|string Number of days between start date and end date or an error 749 */ 750 public static function DAYS($endDate = 0, $startDate = 0) 751 { 752 $startDate = Functions::flattenSingleValue($startDate); 753 $endDate = Functions::flattenSingleValue($endDate); 754 755 $startDate = self::getDateValue($startDate); 756 if (is_string($startDate)) { 757 return Functions::VALUE(); 758 } 759 760 $endDate = self::getDateValue($endDate); 761 if (is_string($endDate)) { 762 return Functions::VALUE(); 763 } 764 765 // Execute function 766 $PHPStartDateObject = Date::excelToDateTimeObject($startDate); 767 $PHPEndDateObject = Date::excelToDateTimeObject($endDate); 768 769 $diff = $PHPStartDateObject->diff($PHPEndDateObject); 770 $days = $diff->days; 771 772 if ($diff->invert) { 773 $days = -$days; 774 } 775 776 return $days; 777 } 778 779 /** 780 * DAYS360. 781 * 782 * Returns the number of days between two dates based on a 360-day year (twelve 30-day months), 783 * which is used in some accounting calculations. Use this function to help compute payments if 784 * your accounting system is based on twelve 30-day months. 785 * 786 * Excel Function: 787 * DAYS360(startDate,endDate[,method]) 788 * 789 * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), 790 * PHP DateTime object, or a standard date string 791 * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), 792 * PHP DateTime object, or a standard date string 793 * @param bool $method US or European Method 794 * FALSE or omitted: U.S. (NASD) method. If the starting date is 795 * the last day of a month, it becomes equal to the 30th of the 796 * same month. If the ending date is the last day of a month and 797 * the starting date is earlier than the 30th of a month, the 798 * ending date becomes equal to the 1st of the next month; 799 * otherwise the ending date becomes equal to the 30th of the 800 * same month. 801 * TRUE: European method. Starting dates and ending dates that 802 * occur on the 31st of a month become equal to the 30th of the 803 * same month. 804 * 805 * @return int|string Number of days between start date and end date 806 */ 807 public static function DAYS360($startDate = 0, $endDate = 0, $method = false) 808 { 809 $startDate = Functions::flattenSingleValue($startDate); 810 $endDate = Functions::flattenSingleValue($endDate); 811 812 if (is_string($startDate = self::getDateValue($startDate))) { 813 return Functions::VALUE(); 814 } 815 if (is_string($endDate = self::getDateValue($endDate))) { 816 return Functions::VALUE(); 817 } 818 819 if (!is_bool($method)) { 820 return Functions::VALUE(); 821 } 822 823 // Execute function 824 $PHPStartDateObject = Date::excelToDateTimeObject($startDate); 825 $startDay = $PHPStartDateObject->format('j'); 826 $startMonth = $PHPStartDateObject->format('n'); 827 $startYear = $PHPStartDateObject->format('Y'); 828 829 $PHPEndDateObject = Date::excelToDateTimeObject($endDate); 830 $endDay = $PHPEndDateObject->format('j'); 831 $endMonth = $PHPEndDateObject->format('n'); 832 $endYear = $PHPEndDateObject->format('Y'); 833 834 return self::dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, !$method); 835 } 836 837 /** 838 * YEARFRAC. 839 * 840 * Calculates the fraction of the year represented by the number of whole days between two dates 841 * (the start_date and the end_date). 842 * Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or 843 * obligations to assign to a specific term. 844 * 845 * Excel Function: 846 * YEARFRAC(startDate,endDate[,method]) 847 * See https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html 848 * for description of algorithm used in Excel 849 * 850 * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), 851 * PHP DateTime object, or a standard date string 852 * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), 853 * PHP DateTime object, or a standard date string 854 * @param int $method Method used for the calculation 855 * 0 or omitted US (NASD) 30/360 856 * 1 Actual/actual 857 * 2 Actual/360 858 * 3 Actual/365 859 * 4 European 30/360 860 * 861 * @return float|string fraction of the year, or a string containing an error 862 */ 863 public static function YEARFRAC($startDate = 0, $endDate = 0, $method = 0) 864 { 865 $startDate = Functions::flattenSingleValue($startDate); 866 $endDate = Functions::flattenSingleValue($endDate); 867 $method = Functions::flattenSingleValue($method); 868 869 if (is_string($startDate = self::getDateValue($startDate))) { 870 return Functions::VALUE(); 871 } 872 if (is_string($endDate = self::getDateValue($endDate))) { 873 return Functions::VALUE(); 874 } 875 if ($startDate > $endDate) { 876 $temp = $startDate; 877 $startDate = $endDate; 878 $endDate = $temp; 879 } 880 881 if (((is_numeric($method)) && (!is_string($method))) || ($method == '')) { 882 switch ($method) { 883 case 0: 884 return self::DAYS360($startDate, $endDate) / 360; 885 case 1: 886 $days = self::DATEDIF($startDate, $endDate); 887 $startYear = self::YEAR($startDate); 888 $endYear = self::YEAR($endDate); 889 $years = $endYear - $startYear + 1; 890 $startMonth = self::MONTHOFYEAR($startDate); 891 $startDay = self::DAYOFMONTH($startDate); 892 $endMonth = self::MONTHOFYEAR($endDate); 893 $endDay = self::DAYOFMONTH($endDate); 894 $startMonthDay = 100 * $startMonth + $startDay; 895 $endMonthDay = 100 * $endMonth + $endDay; 896 if ($years == 1) { 897 if (self::isLeapYear($endYear)) { 898 $tmpCalcAnnualBasis = 366; 899 } else { 900 $tmpCalcAnnualBasis = 365; 901 } 902 } elseif ($years == 2 && $startMonthDay >= $endMonthDay) { 903 if (self::isLeapYear($startYear)) { 904 if ($startMonthDay <= 229) { 905 $tmpCalcAnnualBasis = 366; 906 } else { 907 $tmpCalcAnnualBasis = 365; 908 } 909 } elseif (self::isLeapYear($endYear)) { 910 if ($endMonthDay >= 229) { 911 $tmpCalcAnnualBasis = 366; 912 } else { 913 $tmpCalcAnnualBasis = 365; 914 } 915 } else { 916 $tmpCalcAnnualBasis = 365; 917 } 918 } else { 919 $tmpCalcAnnualBasis = 0; 920 for ($year = $startYear; $year <= $endYear; ++$year) { 921 $tmpCalcAnnualBasis += self::isLeapYear($year) ? 366 : 365; 922 } 923 $tmpCalcAnnualBasis /= $years; 924 } 925 926 return $days / $tmpCalcAnnualBasis; 927 case 2: 928 return self::DATEDIF($startDate, $endDate) / 360; 929 case 3: 930 return self::DATEDIF($startDate, $endDate) / 365; 931 case 4: 932 return self::DAYS360($startDate, $endDate, true) / 360; 933 } 934 } 935 936 return Functions::VALUE(); 937 } 938 939 /** 940 * NETWORKDAYS. 941 * 942 * Returns the number of whole working days between start_date and end_date. Working days 943 * exclude weekends and any dates identified in holidays. 944 * Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days 945 * worked during a specific term. 946 * 947 * Excel Function: 948 * NETWORKDAYS(startDate,endDate[,holidays[,holiday[,...]]]) 949 * 950 * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), 951 * PHP DateTime object, or a standard date string 952 * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), 953 * PHP DateTime object, or a standard date string 954 * 955 * @return int|string Interval between the dates 956 */ 957 public static function NETWORKDAYS($startDate, $endDate, ...$dateArgs) 958 { 959 // Retrieve the mandatory start and end date that are referenced in the function definition 960 $startDate = Functions::flattenSingleValue($startDate); 961 $endDate = Functions::flattenSingleValue($endDate); 962 // Get the optional days 963 $dateArgs = Functions::flattenArray($dateArgs); 964 965 // Validate the start and end dates 966 if (is_string($startDate = $sDate = self::getDateValue($startDate))) { 967 return Functions::VALUE(); 968 } 969 $startDate = (float) floor($startDate); 970 if (is_string($endDate = $eDate = self::getDateValue($endDate))) { 971 return Functions::VALUE(); 972 } 973 $endDate = (float) floor($endDate); 974 975 if ($sDate > $eDate) { 976 $startDate = $eDate; 977 $endDate = $sDate; 978 } 979 980 // Execute function 981 $startDoW = 6 - self::WEEKDAY($startDate, 2); 982 if ($startDoW < 0) { 983 $startDoW = 0; 984 } 985 $endDoW = self::WEEKDAY($endDate, 2); 986 if ($endDoW >= 6) { 987 $endDoW = 0; 988 } 989 990 $wholeWeekDays = floor(($endDate - $startDate) / 7) * 5; 991 $partWeekDays = $endDoW + $startDoW; 992 if ($partWeekDays > 5) { 993 $partWeekDays -= 5; 994 } 995 996 // Test any extra holiday parameters 997 $holidayCountedArray = []; 998 foreach ($dateArgs as $holidayDate) { 999 if (is_string($holidayDate = self::getDateValue($holidayDate))) { 1000 return Functions::VALUE(); 1001 } 1002 if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) { 1003 if ((self::WEEKDAY($holidayDate, 2) < 6) && (!in_array($holidayDate, $holidayCountedArray))) { 1004 --$partWeekDays; 1005 $holidayCountedArray[] = $holidayDate; 1006 } 1007 } 1008 } 1009 1010 if ($sDate > $eDate) { 1011 return 0 - ($wholeWeekDays + $partWeekDays); 1012 } 1013 1014 return $wholeWeekDays + $partWeekDays; 1015 } 1016 1017 /** 1018 * WORKDAY. 1019 * 1020 * Returns the date that is the indicated number of working days before or after a date (the 1021 * starting date). Working days exclude weekends and any dates identified as holidays. 1022 * Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected 1023 * delivery times, or the number of days of work performed. 1024 * 1025 * Excel Function: 1026 * WORKDAY(startDate,endDays[,holidays[,holiday[,...]]]) 1027 * 1028 * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), 1029 * PHP DateTime object, or a standard date string 1030 * @param int $endDays The number of nonweekend and nonholiday days before or after 1031 * startDate. A positive value for days yields a future date; a 1032 * negative value yields a past date. 1033 * 1034 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 1035 * depending on the value of the ReturnDateType flag 1036 */ 1037 public static function WORKDAY($startDate, $endDays, ...$dateArgs) 1038 { 1039 // Retrieve the mandatory start date and days that are referenced in the function definition 1040 $startDate = Functions::flattenSingleValue($startDate); 1041 $endDays = Functions::flattenSingleValue($endDays); 1042 // Get the optional days 1043 $dateArgs = Functions::flattenArray($dateArgs); 1044 1045 if ((is_string($startDate = self::getDateValue($startDate))) || (!is_numeric($endDays))) { 1046 return Functions::VALUE(); 1047 } 1048 $startDate = (float) floor($startDate); 1049 $endDays = (int) floor($endDays); 1050 // If endDays is 0, we always return startDate 1051 if ($endDays == 0) { 1052 return $startDate; 1053 } 1054 1055 $decrementing = $endDays < 0; 1056 1057 // Adjust the start date if it falls over a weekend 1058 1059 $startDoW = self::WEEKDAY($startDate, 3); 1060 if (self::WEEKDAY($startDate, 3) >= 5) { 1061 $startDate += ($decrementing) ? -$startDoW + 4 : 7 - $startDoW; 1062 ($decrementing) ? $endDays++ : $endDays--; 1063 } 1064 1065 // Add endDays 1066 $endDate = (float) $startDate + ((int) ($endDays / 5) * 7) + ($endDays % 5); 1067 1068 // Adjust the calculated end date if it falls over a weekend 1069 $endDoW = self::WEEKDAY($endDate, 3); 1070 if ($endDoW >= 5) { 1071 $endDate += ($decrementing) ? -$endDoW + 4 : 7 - $endDoW; 1072 } 1073 1074 // Test any extra holiday parameters 1075 if (!empty($dateArgs)) { 1076 $holidayCountedArray = $holidayDates = []; 1077 foreach ($dateArgs as $holidayDate) { 1078 if (($holidayDate !== null) && (trim($holidayDate) > '')) { 1079 if (is_string($holidayDate = self::getDateValue($holidayDate))) { 1080 return Functions::VALUE(); 1081 } 1082 if (self::WEEKDAY($holidayDate, 3) < 5) { 1083 $holidayDates[] = $holidayDate; 1084 } 1085 } 1086 } 1087 if ($decrementing) { 1088 rsort($holidayDates, SORT_NUMERIC); 1089 } else { 1090 sort($holidayDates, SORT_NUMERIC); 1091 } 1092 foreach ($holidayDates as $holidayDate) { 1093 if ($decrementing) { 1094 if (($holidayDate <= $startDate) && ($holidayDate >= $endDate)) { 1095 if (!in_array($holidayDate, $holidayCountedArray)) { 1096 --$endDate; 1097 $holidayCountedArray[] = $holidayDate; 1098 } 1099 } 1100 } else { 1101 if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) { 1102 if (!in_array($holidayDate, $holidayCountedArray)) { 1103 ++$endDate; 1104 $holidayCountedArray[] = $holidayDate; 1105 } 1106 } 1107 } 1108 // Adjust the calculated end date if it falls over a weekend 1109 $endDoW = self::WEEKDAY($endDate, 3); 1110 if ($endDoW >= 5) { 1111 $endDate += ($decrementing) ? -$endDoW + 4 : 7 - $endDoW; 1112 } 1113 } 1114 } 1115 1116 switch (Functions::getReturnDateType()) { 1117 case Functions::RETURNDATE_EXCEL: 1118 return (float) $endDate; 1119 case Functions::RETURNDATE_UNIX_TIMESTAMP: 1120 return (int) Date::excelToTimestamp($endDate); 1121 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 1122 return Date::excelToDateTimeObject($endDate); 1123 } 1124 } 1125 1126 /** 1127 * DAYOFMONTH. 1128 * 1129 * Returns the day of the month, for a specified date. The day is given as an integer 1130 * ranging from 1 to 31. 1131 * 1132 * Excel Function: 1133 * DAY(dateValue) 1134 * 1135 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1136 * PHP DateTime object, or a standard date string 1137 * 1138 * @return int|string Day of the month 1139 */ 1140 public static function DAYOFMONTH($dateValue = 1) 1141 { 1142 $dateValue = Functions::flattenSingleValue($dateValue); 1143 1144 if ($dateValue === null) { 1145 $dateValue = 1; 1146 } elseif (is_string($dateValue = self::getDateValue($dateValue))) { 1147 return Functions::VALUE(); 1148 } 1149 1150 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) { 1151 if ($dateValue < 0.0) { 1152 return Functions::NAN(); 1153 } elseif ($dateValue < 1.0) { 1154 return 0; 1155 } 1156 } 1157 1158 // Execute function 1159 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1160 1161 return (int) $PHPDateObject->format('j'); 1162 } 1163 1164 /** 1165 * WEEKDAY. 1166 * 1167 * Returns the day of the week for a specified date. The day is given as an integer 1168 * ranging from 0 to 7 (dependent on the requested style). 1169 * 1170 * Excel Function: 1171 * WEEKDAY(dateValue[,style]) 1172 * 1173 * @param int $dateValue Excel date serial value (float), PHP date timestamp (integer), 1174 * PHP DateTime object, or a standard date string 1175 * @param int $style A number that determines the type of return value 1176 * 1 or omitted Numbers 1 (Sunday) through 7 (Saturday). 1177 * 2 Numbers 1 (Monday) through 7 (Sunday). 1178 * 3 Numbers 0 (Monday) through 6 (Sunday). 1179 * 1180 * @return int|string Day of the week value 1181 */ 1182 public static function WEEKDAY($dateValue = 1, $style = 1) 1183 { 1184 $dateValue = Functions::flattenSingleValue($dateValue); 1185 $style = Functions::flattenSingleValue($style); 1186 1187 if (!is_numeric($style)) { 1188 return Functions::VALUE(); 1189 } elseif (($style < 1) || ($style > 3)) { 1190 return Functions::NAN(); 1191 } 1192 $style = floor($style); 1193 1194 if ($dateValue === null) { 1195 $dateValue = 1; 1196 } elseif (is_string($dateValue = self::getDateValue($dateValue))) { 1197 return Functions::VALUE(); 1198 } elseif ($dateValue < 0.0) { 1199 return Functions::NAN(); 1200 } 1201 1202 // Execute function 1203 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1204 $DoW = (int) $PHPDateObject->format('w'); 1205 1206 $firstDay = 1; 1207 switch ($style) { 1208 case 1: 1209 ++$DoW; 1210 1211 break; 1212 case 2: 1213 if ($DoW === 0) { 1214 $DoW = 7; 1215 } 1216 1217 break; 1218 case 3: 1219 if ($DoW === 0) { 1220 $DoW = 7; 1221 } 1222 $firstDay = 0; 1223 --$DoW; 1224 1225 break; 1226 } 1227 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) { 1228 // Test for Excel's 1900 leap year, and introduce the error as required 1229 if (($PHPDateObject->format('Y') == 1900) && ($PHPDateObject->format('n') <= 2)) { 1230 --$DoW; 1231 if ($DoW < $firstDay) { 1232 $DoW += 7; 1233 } 1234 } 1235 } 1236 1237 return $DoW; 1238 } 1239 1240 const STARTWEEK_SUNDAY = 1; 1241 const STARTWEEK_MONDAY = 2; 1242 const STARTWEEK_MONDAY_ALT = 11; 1243 const STARTWEEK_TUESDAY = 12; 1244 const STARTWEEK_WEDNESDAY = 13; 1245 const STARTWEEK_THURSDAY = 14; 1246 const STARTWEEK_FRIDAY = 15; 1247 const STARTWEEK_SATURDAY = 16; 1248 const STARTWEEK_SUNDAY_ALT = 17; 1249 const DOW_SUNDAY = 1; 1250 const DOW_MONDAY = 2; 1251 const DOW_TUESDAY = 3; 1252 const DOW_WEDNESDAY = 4; 1253 const DOW_THURSDAY = 5; 1254 const DOW_FRIDAY = 6; 1255 const DOW_SATURDAY = 7; 1256 const STARTWEEK_MONDAY_ISO = 21; 1257 const METHODARR = [ 1258 self::STARTWEEK_SUNDAY => self::DOW_SUNDAY, 1259 self::DOW_MONDAY, 1260 self::STARTWEEK_MONDAY_ALT => self::DOW_MONDAY, 1261 self::DOW_TUESDAY, 1262 self::DOW_WEDNESDAY, 1263 self::DOW_THURSDAY, 1264 self::DOW_FRIDAY, 1265 self::DOW_SATURDAY, 1266 self::DOW_SUNDAY, 1267 self::STARTWEEK_MONDAY_ISO => self::STARTWEEK_MONDAY_ISO, 1268 ]; 1269 1270 /** 1271 * WEEKNUM. 1272 * 1273 * Returns the week of the year for a specified date. 1274 * The WEEKNUM function considers the week containing January 1 to be the first week of the year. 1275 * However, there is a European standard that defines the first week as the one with the majority 1276 * of days (four or more) falling in the new year. This means that for years in which there are 1277 * three days or less in the first week of January, the WEEKNUM function returns week numbers 1278 * that are incorrect according to the European standard. 1279 * 1280 * Excel Function: 1281 * WEEKNUM(dateValue[,style]) 1282 * 1283 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1284 * PHP DateTime object, or a standard date string 1285 * @param int $method Week begins on Sunday or Monday 1286 * 1 or omitted Week begins on Sunday. 1287 * 2 Week begins on Monday. 1288 * 11 Week begins on Monday. 1289 * 12 Week begins on Tuesday. 1290 * 13 Week begins on Wednesday. 1291 * 14 Week begins on Thursday. 1292 * 15 Week begins on Friday. 1293 * 16 Week begins on Saturday. 1294 * 17 Week begins on Sunday. 1295 * 21 ISO (Jan. 4 is week 1, begins on Monday). 1296 * 1297 * @return int|string Week Number 1298 */ 1299 public static function WEEKNUM($dateValue = 1, $method = self::STARTWEEK_SUNDAY) 1300 { 1301 $dateValue = Functions::flattenSingleValue($dateValue); 1302 $method = Functions::flattenSingleValue($method); 1303 1304 if (!is_numeric($method)) { 1305 return Functions::VALUE(); 1306 } 1307 $method = (int) $method; 1308 if (!array_key_exists($method, self::METHODARR)) { 1309 return Functions::NaN(); 1310 } 1311 $method = self::METHODARR[$method]; 1312 1313 $dateValue = self::getDateValue($dateValue); 1314 if (is_string($dateValue)) { 1315 return Functions::VALUE(); 1316 } 1317 if ($dateValue < 0.0) { 1318 return Functions::NAN(); 1319 } 1320 1321 // Execute function 1322 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1323 if ($method == self::STARTWEEK_MONDAY_ISO) { 1324 return (int) $PHPDateObject->format('W'); 1325 } 1326 $dayOfYear = $PHPDateObject->format('z'); 1327 $PHPDateObject->modify('-' . $dayOfYear . ' days'); 1328 $firstDayOfFirstWeek = $PHPDateObject->format('w'); 1329 $daysInFirstWeek = (6 - $firstDayOfFirstWeek + $method) % 7; 1330 $daysInFirstWeek += 7 * !$daysInFirstWeek; 1331 $endFirstWeek = $daysInFirstWeek - 1; 1332 $weekOfYear = floor(($dayOfYear - $endFirstWeek + 13) / 7); 1333 1334 return (int) $weekOfYear; 1335 } 1336 1337 /** 1338 * ISOWEEKNUM. 1339 * 1340 * Returns the ISO 8601 week number of the year for a specified date. 1341 * 1342 * Excel Function: 1343 * ISOWEEKNUM(dateValue) 1344 * 1345 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1346 * PHP DateTime object, or a standard date string 1347 * 1348 * @return int|string Week Number 1349 */ 1350 public static function ISOWEEKNUM($dateValue = 1) 1351 { 1352 $dateValue = Functions::flattenSingleValue($dateValue); 1353 1354 if ($dateValue === null) { 1355 $dateValue = 1; 1356 } elseif (is_string($dateValue = self::getDateValue($dateValue))) { 1357 return Functions::VALUE(); 1358 } elseif ($dateValue < 0.0) { 1359 return Functions::NAN(); 1360 } 1361 1362 // Execute function 1363 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1364 1365 return (int) $PHPDateObject->format('W'); 1366 } 1367 1368 /** 1369 * MONTHOFYEAR. 1370 * 1371 * Returns the month of a date represented by a serial number. 1372 * The month is given as an integer, ranging from 1 (January) to 12 (December). 1373 * 1374 * Excel Function: 1375 * MONTH(dateValue) 1376 * 1377 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1378 * PHP DateTime object, or a standard date string 1379 * 1380 * @return int|string Month of the year 1381 */ 1382 public static function MONTHOFYEAR($dateValue = 1) 1383 { 1384 $dateValue = Functions::flattenSingleValue($dateValue); 1385 1386 if (empty($dateValue)) { 1387 $dateValue = 1; 1388 } 1389 if (is_string($dateValue = self::getDateValue($dateValue))) { 1390 return Functions::VALUE(); 1391 } elseif ($dateValue < 0.0) { 1392 return Functions::NAN(); 1393 } 1394 1395 // Execute function 1396 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1397 1398 return (int) $PHPDateObject->format('n'); 1399 } 1400 1401 /** 1402 * YEAR. 1403 * 1404 * Returns the year corresponding to a date. 1405 * The year is returned as an integer in the range 1900-9999. 1406 * 1407 * Excel Function: 1408 * YEAR(dateValue) 1409 * 1410 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1411 * PHP DateTime object, or a standard date string 1412 * 1413 * @return int|string Year 1414 */ 1415 public static function YEAR($dateValue = 1) 1416 { 1417 $dateValue = Functions::flattenSingleValue($dateValue); 1418 1419 if ($dateValue === null) { 1420 $dateValue = 1; 1421 } elseif (is_string($dateValue = self::getDateValue($dateValue))) { 1422 return Functions::VALUE(); 1423 } elseif ($dateValue < 0.0) { 1424 return Functions::NAN(); 1425 } 1426 1427 // Execute function 1428 $PHPDateObject = Date::excelToDateTimeObject($dateValue); 1429 1430 return (int) $PHPDateObject->format('Y'); 1431 } 1432 1433 /** 1434 * HOUROFDAY. 1435 * 1436 * Returns the hour of a time value. 1437 * The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). 1438 * 1439 * Excel Function: 1440 * HOUR(timeValue) 1441 * 1442 * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), 1443 * PHP DateTime object, or a standard time string 1444 * 1445 * @return int|string Hour 1446 */ 1447 public static function HOUROFDAY($timeValue = 0) 1448 { 1449 $timeValue = Functions::flattenSingleValue($timeValue); 1450 1451 if (!is_numeric($timeValue)) { 1452 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 1453 $testVal = strtok($timeValue, '/-: '); 1454 if (strlen($testVal) < strlen($timeValue)) { 1455 return Functions::VALUE(); 1456 } 1457 } 1458 $timeValue = self::getTimeValue($timeValue); 1459 if (is_string($timeValue)) { 1460 return Functions::VALUE(); 1461 } 1462 } 1463 // Execute function 1464 if ($timeValue >= 1) { 1465 $timeValue = fmod($timeValue, 1); 1466 } elseif ($timeValue < 0.0) { 1467 return Functions::NAN(); 1468 } 1469 $timeValue = Date::excelToTimestamp($timeValue); 1470 1471 return (int) gmdate('G', $timeValue); 1472 } 1473 1474 /** 1475 * MINUTE. 1476 * 1477 * Returns the minutes of a time value. 1478 * The minute is given as an integer, ranging from 0 to 59. 1479 * 1480 * Excel Function: 1481 * MINUTE(timeValue) 1482 * 1483 * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), 1484 * PHP DateTime object, or a standard time string 1485 * 1486 * @return int|string Minute 1487 */ 1488 public static function MINUTE($timeValue = 0) 1489 { 1490 $timeValue = $timeTester = Functions::flattenSingleValue($timeValue); 1491 1492 if (!is_numeric($timeValue)) { 1493 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 1494 $testVal = strtok($timeValue, '/-: '); 1495 if (strlen($testVal) < strlen($timeValue)) { 1496 return Functions::VALUE(); 1497 } 1498 } 1499 $timeValue = self::getTimeValue($timeValue); 1500 if (is_string($timeValue)) { 1501 return Functions::VALUE(); 1502 } 1503 } 1504 // Execute function 1505 if ($timeValue >= 1) { 1506 $timeValue = fmod($timeValue, 1); 1507 } elseif ($timeValue < 0.0) { 1508 return Functions::NAN(); 1509 } 1510 $timeValue = Date::excelToTimestamp($timeValue); 1511 1512 return (int) gmdate('i', $timeValue); 1513 } 1514 1515 /** 1516 * SECOND. 1517 * 1518 * Returns the seconds of a time value. 1519 * The second is given as an integer in the range 0 (zero) to 59. 1520 * 1521 * Excel Function: 1522 * SECOND(timeValue) 1523 * 1524 * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), 1525 * PHP DateTime object, or a standard time string 1526 * 1527 * @return int|string Second 1528 */ 1529 public static function SECOND($timeValue = 0) 1530 { 1531 $timeValue = Functions::flattenSingleValue($timeValue); 1532 1533 if (!is_numeric($timeValue)) { 1534 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 1535 $testVal = strtok($timeValue, '/-: '); 1536 if (strlen($testVal) < strlen($timeValue)) { 1537 return Functions::VALUE(); 1538 } 1539 } 1540 $timeValue = self::getTimeValue($timeValue); 1541 if (is_string($timeValue)) { 1542 return Functions::VALUE(); 1543 } 1544 } 1545 // Execute function 1546 if ($timeValue >= 1) { 1547 $timeValue = fmod($timeValue, 1); 1548 } elseif ($timeValue < 0.0) { 1549 return Functions::NAN(); 1550 } 1551 $timeValue = Date::excelToTimestamp($timeValue); 1552 1553 return (int) gmdate('s', $timeValue); 1554 } 1555 1556 /** 1557 * EDATE. 1558 * 1559 * Returns the serial number that represents the date that is the indicated number of months 1560 * before or after a specified date (the start_date). 1561 * Use EDATE to calculate maturity dates or due dates that fall on the same day of the month 1562 * as the date of issue. 1563 * 1564 * Excel Function: 1565 * EDATE(dateValue,adjustmentMonths) 1566 * 1567 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1568 * PHP DateTime object, or a standard date string 1569 * @param int $adjustmentMonths The number of months before or after start_date. 1570 * A positive value for months yields a future date; 1571 * a negative value yields a past date. 1572 * 1573 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 1574 * depending on the value of the ReturnDateType flag 1575 */ 1576 public static function EDATE($dateValue = 1, $adjustmentMonths = 0) 1577 { 1578 $dateValue = Functions::flattenSingleValue($dateValue); 1579 $adjustmentMonths = Functions::flattenSingleValue($adjustmentMonths); 1580 1581 if (!is_numeric($adjustmentMonths)) { 1582 return Functions::VALUE(); 1583 } 1584 $adjustmentMonths = floor($adjustmentMonths); 1585 1586 if (is_string($dateValue = self::getDateValue($dateValue))) { 1587 return Functions::VALUE(); 1588 } 1589 1590 // Execute function 1591 $PHPDateObject = self::adjustDateByMonths($dateValue, $adjustmentMonths); 1592 1593 switch (Functions::getReturnDateType()) { 1594 case Functions::RETURNDATE_EXCEL: 1595 return (float) Date::PHPToExcel($PHPDateObject); 1596 case Functions::RETURNDATE_UNIX_TIMESTAMP: 1597 return (int) Date::excelToTimestamp(Date::PHPToExcel($PHPDateObject)); 1598 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 1599 return $PHPDateObject; 1600 } 1601 } 1602 1603 /** 1604 * EOMONTH. 1605 * 1606 * Returns the date value for the last day of the month that is the indicated number of months 1607 * before or after start_date. 1608 * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. 1609 * 1610 * Excel Function: 1611 * EOMONTH(dateValue,adjustmentMonths) 1612 * 1613 * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), 1614 * PHP DateTime object, or a standard date string 1615 * @param int $adjustmentMonths The number of months before or after start_date. 1616 * A positive value for months yields a future date; 1617 * a negative value yields a past date. 1618 * 1619 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 1620 * depending on the value of the ReturnDateType flag 1621 */ 1622 public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) 1623 { 1624 $dateValue = Functions::flattenSingleValue($dateValue); 1625 $adjustmentMonths = Functions::flattenSingleValue($adjustmentMonths); 1626 1627 if (!is_numeric($adjustmentMonths)) { 1628 return Functions::VALUE(); 1629 } 1630 $adjustmentMonths = floor($adjustmentMonths); 1631 1632 if (is_string($dateValue = self::getDateValue($dateValue))) { 1633 return Functions::VALUE(); 1634 } 1635 1636 // Execute function 1637 $PHPDateObject = self::adjustDateByMonths($dateValue, $adjustmentMonths + 1); 1638 $adjustDays = (int) $PHPDateObject->format('d'); 1639 $adjustDaysString = '-' . $adjustDays . ' days'; 1640 $PHPDateObject->modify($adjustDaysString); 1641 1642 switch (Functions::getReturnDateType()) { 1643 case Functions::RETURNDATE_EXCEL: 1644 return (float) Date::PHPToExcel($PHPDateObject); 1645 case Functions::RETURNDATE_UNIX_TIMESTAMP: 1646 return (int) Date::excelToTimestamp(Date::PHPToExcel($PHPDateObject)); 1647 case Functions::RETURNDATE_PHP_DATETIME_OBJECT: 1648 return $PHPDateObject; 1649 } 1650 } 1651 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
