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