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