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