PHPOffice /
PhpSpreadsheet
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like DateTime often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use DateTime, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 27 | class DateTime |
||
| 28 | { |
||
| 29 | /** |
||
| 30 | * Identify if a year is a leap year or not |
||
| 31 | * |
||
| 32 | * @param int $year The year to test |
||
| 33 | * @return bool TRUE if the year is a leap year, otherwise FALSE |
||
| 34 | */ |
||
| 35 | 8 | public static function isLeapYear($year) |
|
| 39 | |||
| 40 | /** |
||
| 41 | * Return the number of days between two dates based on a 360 day calendar |
||
| 42 | * |
||
| 43 | * @param int $startDay Day of month of the start date |
||
| 44 | * @param int $startMonth Month of the start date |
||
| 45 | * @param int $startYear Year of the start date |
||
| 46 | * @param int $endDay Day of month of the start date |
||
| 47 | * @param int $endMonth Month of the start date |
||
| 48 | * @param int $endYear Year of the start date |
||
| 49 | * @param bool $methodUS Whether to use the US method or the European method of calculation |
||
| 50 | * @return int Number of days between the start date and the end date |
||
| 51 | */ |
||
| 52 | 42 | private static function dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, $methodUS) |
|
| 75 | |||
| 76 | /** |
||
| 77 | * getDateValue |
||
| 78 | * |
||
| 79 | * @param string $dateValue |
||
| 80 | * @return mixed Excel date/time serial value, or string if error |
||
| 81 | */ |
||
| 82 | 191 | public static function getDateValue($dateValue) |
|
| 101 | |||
| 102 | /** |
||
| 103 | * getTimeValue |
||
| 104 | * |
||
| 105 | * @param string $timeValue |
||
| 106 | * @return mixed Excel date/time serial value, or string if error |
||
| 107 | */ |
||
| 108 | 4 | private static function getTimeValue($timeValue) |
|
| 117 | |||
| 118 | 32 | private static function adjustDateByMonths($dateValue = 0, $adjustmentMonths = 0) |
|
| 144 | |||
| 145 | /** |
||
| 146 | * DATETIMENOW |
||
| 147 | * |
||
| 148 | * Returns the current date and time. |
||
| 149 | * The NOW function is useful when you need to display the current date and time on a worksheet or |
||
| 150 | * calculate a value based on the current date and time, and have that value updated each time you |
||
| 151 | * open the worksheet. |
||
| 152 | * |
||
| 153 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date |
||
| 154 | * and time format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 155 | * |
||
| 156 | * Excel Function: |
||
| 157 | * NOW() |
||
| 158 | * |
||
| 159 | * @category Date/Time Functions |
||
| 160 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 161 | * depending on the value of the ReturnDateType flag |
||
| 162 | */ |
||
| 163 | public static function DATETIMENOW() |
||
| 183 | |||
| 184 | /** |
||
| 185 | * DATENOW |
||
| 186 | * |
||
| 187 | * Returns the current date. |
||
| 188 | * The NOW function is useful when you need to display the current date and time on a worksheet or |
||
| 189 | * calculate a value based on the current date and time, and have that value updated each time you |
||
| 190 | * open the worksheet. |
||
| 191 | * |
||
| 192 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date |
||
| 193 | * and time format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 194 | * |
||
| 195 | * Excel Function: |
||
| 196 | * TODAY() |
||
| 197 | * |
||
| 198 | * @category Date/Time Functions |
||
| 199 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 200 | * depending on the value of the ReturnDateType flag |
||
| 201 | */ |
||
| 202 | 1 | public static function DATENOW() |
|
| 203 | { |
||
| 204 | 1 | $saveTimeZone = date_default_timezone_get(); |
|
| 205 | 1 | date_default_timezone_set('UTC'); |
|
| 206 | 1 | $retValue = false; |
|
| 207 | 1 | $excelDateTime = floor(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(time())); |
|
| 208 | 1 | switch (Functions::getReturnDateType()) { |
|
| 209 | 1 | case Functions::RETURNDATE_EXCEL: |
|
| 210 | $retValue = (float) $excelDateTime; |
||
| 211 | break; |
||
| 212 | 1 | case Functions::RETURNDATE_PHP_NUMERIC: |
|
| 213 | 1 | $retValue = (integer) \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($excelDateTime); |
|
| 214 | 1 | break; |
|
| 215 | case Functions::RETURNDATE_PHP_OBJECT: |
||
| 216 | $retValue = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($excelDateTime); |
||
| 217 | break; |
||
| 218 | } |
||
| 219 | 1 | date_default_timezone_set($saveTimeZone); |
|
| 220 | |||
| 221 | 1 | return $retValue; |
|
| 222 | } |
||
| 223 | |||
| 224 | /** |
||
| 225 | * DATE |
||
| 226 | * |
||
| 227 | * The DATE function returns a value that represents a particular date. |
||
| 228 | * |
||
| 229 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date |
||
| 230 | * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 231 | * |
||
| 232 | * Excel Function: |
||
| 233 | * DATE(year,month,day) |
||
| 234 | * |
||
| 235 | * PhpSpreadsheet is a lot more forgiving than MS Excel when passing non numeric values to this function. |
||
| 236 | * A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted, |
||
| 237 | * as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language. |
||
| 238 | * |
||
| 239 | * @category Date/Time Functions |
||
| 240 | * @param int $year The value of the year argument can include one to four digits. |
||
| 241 | * Excel interprets the year argument according to the configured |
||
| 242 | * date system: 1900 or 1904. |
||
| 243 | * If year is between 0 (zero) and 1899 (inclusive), Excel adds that |
||
| 244 | * value to 1900 to calculate the year. For example, DATE(108,1,2) |
||
| 245 | * returns January 2, 2008 (1900+108). |
||
| 246 | * If year is between 1900 and 9999 (inclusive), Excel uses that |
||
| 247 | * value as the year. For example, DATE(2008,1,2) returns January 2, |
||
| 248 | * 2008. |
||
| 249 | * If year is less than 0 or is 10000 or greater, Excel returns the |
||
| 250 | * #NUM! error value. |
||
| 251 | * @param int $month A positive or negative integer representing the month of the year |
||
| 252 | * from 1 to 12 (January to December). |
||
| 253 | * If month is greater than 12, month adds that number of months to |
||
| 254 | * the first month in the year specified. For example, DATE(2008,14,2) |
||
| 255 | * returns the serial number representing February 2, 2009. |
||
| 256 | * If month is less than 1, month subtracts the magnitude of that |
||
| 257 | * number of months, plus 1, from the first month in the year |
||
| 258 | * specified. For example, DATE(2008,-3,2) returns the serial number |
||
| 259 | * representing September 2, 2007. |
||
| 260 | * @param int $day A positive or negative integer representing the day of the month |
||
| 261 | * from 1 to 31. |
||
| 262 | * If day is greater than the number of days in the month specified, |
||
| 263 | * day adds that number of days to the first day in the month. For |
||
| 264 | * example, DATE(2008,1,35) returns the serial number representing |
||
| 265 | * February 4, 2008. |
||
| 266 | * If day is less than 1, day subtracts the magnitude that number of |
||
| 267 | * days, plus one, from the first day of the month specified. For |
||
| 268 | * example, DATE(2008,1,-15) returns the serial number representing |
||
| 269 | * December 16, 2007. |
||
| 270 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 271 | * depending on the value of the ReturnDateType flag |
||
| 272 | */ |
||
| 273 | 83 | public static function DATE($year = 0, $month = 1, $day = 1) |
|
| 274 | { |
||
| 275 | 83 | $year = Functions::flattenSingleValue($year); |
|
| 276 | 83 | $month = Functions::flattenSingleValue($month); |
|
| 277 | 83 | $day = Functions::flattenSingleValue($day); |
|
| 278 | |||
| 279 | 83 | if (($month !== null) && (!is_numeric($month))) { |
|
| 280 | 3 | $month = \PhpOffice\PhpSpreadsheet\Shared\Date::monthStringToNumber($month); |
|
|
|
|||
| 281 | } |
||
| 282 | |||
| 283 | 83 | if (($day !== null) && (!is_numeric($day))) { |
|
| 284 | 3 | $day = \PhpOffice\PhpSpreadsheet\Shared\Date::dayStringToNumber($day); |
|
| 285 | } |
||
| 286 | |||
| 287 | 83 | $year = ($year !== null) ? \PhpOffice\PhpSpreadsheet\Shared\StringHelper::testStringAsNumeric($year) : 0; |
|
| 288 | 83 | $month = ($month !== null) ? \PhpOffice\PhpSpreadsheet\Shared\StringHelper::testStringAsNumeric($month) : 0; |
|
| 289 | 83 | $day = ($day !== null) ? \PhpOffice\PhpSpreadsheet\Shared\StringHelper::testStringAsNumeric($day) : 0; |
|
| 290 | 83 | if ((!is_numeric($year)) || |
|
| 291 | 82 | (!is_numeric($month)) || |
|
| 292 | 83 | (!is_numeric($day))) { |
|
| 293 | 3 | return Functions::VALUE(); |
|
| 294 | } |
||
| 295 | 80 | $year = (integer) $year; |
|
| 296 | 80 | $month = (integer) $month; |
|
| 297 | 80 | $day = (integer) $day; |
|
| 298 | |||
| 299 | 80 | $baseYear = \PhpOffice\PhpSpreadsheet\Shared\Date::getExcelCalendar(); |
|
| 300 | // Validate parameters |
||
| 301 | 80 | if ($year < ($baseYear - 1900)) { |
|
| 302 | 2 | return Functions::NAN(); |
|
| 303 | } |
||
| 304 | 78 | if ((($baseYear - 1900) != 0) && ($year < $baseYear) && ($year >= 1900)) { |
|
| 305 | 1 | return Functions::NAN(); |
|
| 306 | } |
||
| 307 | |||
| 308 | 77 | if (($year < $baseYear) && ($year >= ($baseYear - 1900))) { |
|
| 309 | 6 | $year += 1900; |
|
| 310 | } |
||
| 311 | |||
| 312 | 77 | if ($month < 1) { |
|
| 313 | // Handle year/month adjustment if month < 1 |
||
| 314 | 21 | --$month; |
|
| 315 | 21 | $year += ceil($month / 12) - 1; |
|
| 316 | 21 | $month = 13 - abs($month % 12); |
|
| 317 | 56 | } elseif ($month > 12) { |
|
| 318 | // Handle year/month adjustment if month > 12 |
||
| 319 | 7 | $year += floor($month / 12); |
|
| 320 | 7 | $month = ($month % 12); |
|
| 321 | } |
||
| 322 | |||
| 323 | // Re-validate the year parameter after adjustments |
||
| 324 | 77 | if (($year < $baseYear) || ($year >= 10000)) { |
|
| 325 | 2 | return Functions::NAN(); |
|
| 326 | } |
||
| 327 | |||
| 328 | // Execute function |
||
| 329 | 75 | $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel($year, $month, $day); |
|
| 330 | 75 | View Code Duplication | switch (Functions::getReturnDateType()) { |
| 331 | 75 | case Functions::RETURNDATE_EXCEL: |
|
| 332 | 73 | return (float) $excelDateValue; |
|
| 333 | 2 | case Functions::RETURNDATE_PHP_NUMERIC: |
|
| 334 | 1 | return (integer) \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($excelDateValue); |
|
| 335 | 1 | case Functions::RETURNDATE_PHP_OBJECT: |
|
| 336 | 1 | return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($excelDateValue); |
|
| 337 | } |
||
| 338 | } |
||
| 339 | |||
| 340 | /** |
||
| 341 | * TIME |
||
| 342 | * |
||
| 343 | * The TIME function returns a value that represents a particular time. |
||
| 344 | * |
||
| 345 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time |
||
| 346 | * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 347 | * |
||
| 348 | * Excel Function: |
||
| 349 | * TIME(hour,minute,second) |
||
| 350 | * |
||
| 351 | * @category Date/Time Functions |
||
| 352 | * @param int $hour A number from 0 (zero) to 32767 representing the hour. |
||
| 353 | * Any value greater than 23 will be divided by 24 and the remainder |
||
| 354 | * will be treated as the hour value. For example, TIME(27,0,0) = |
||
| 355 | * TIME(3,0,0) = .125 or 3:00 AM. |
||
| 356 | * @param int $minute A number from 0 to 32767 representing the minute. |
||
| 357 | * Any value greater than 59 will be converted to hours and minutes. |
||
| 358 | * For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM. |
||
| 359 | * @param int $second A number from 0 to 32767 representing the second. |
||
| 360 | * Any value greater than 59 will be converted to hours, minutes, |
||
| 361 | * and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 |
||
| 362 | * or 12:33:20 AM |
||
| 363 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 364 | * depending on the value of the ReturnDateType flag |
||
| 365 | */ |
||
| 366 | 23 | public static function TIME($hour = 0, $minute = 0, $second = 0) |
|
| 367 | { |
||
| 368 | 23 | $hour = Functions::flattenSingleValue($hour); |
|
| 369 | 23 | $minute = Functions::flattenSingleValue($minute); |
|
| 370 | 23 | $second = Functions::flattenSingleValue($second); |
|
| 371 | |||
| 372 | 23 | if ($hour == '') { |
|
| 373 | $hour = 0; |
||
| 374 | } |
||
| 375 | 23 | if ($minute == '') { |
|
| 376 | 5 | $minute = 0; |
|
| 377 | } |
||
| 378 | 23 | if ($second == '') { |
|
| 379 | 5 | $second = 0; |
|
| 380 | } |
||
| 381 | |||
| 382 | 23 | if ((!is_numeric($hour)) || (!is_numeric($minute)) || (!is_numeric($second))) { |
|
| 383 | 1 | return Functions::VALUE(); |
|
| 384 | } |
||
| 385 | 22 | $hour = (integer) $hour; |
|
| 386 | 22 | $minute = (integer) $minute; |
|
| 387 | 22 | $second = (integer) $second; |
|
| 388 | |||
| 389 | 22 | View Code Duplication | if ($second < 0) { |
| 390 | 4 | $minute += floor($second / 60); |
|
| 391 | 4 | $second = 60 - abs($second % 60); |
|
| 392 | 4 | if ($second == 60) { |
|
| 393 | 4 | $second = 0; |
|
| 394 | } |
||
| 395 | 18 | } elseif ($second >= 60) { |
|
| 396 | 1 | $minute += floor($second / 60); |
|
| 397 | 1 | $second = $second % 60; |
|
| 398 | } |
||
| 399 | 22 | View Code Duplication | if ($minute < 0) { |
| 400 | 7 | $hour += floor($minute / 60); |
|
| 401 | 7 | $minute = 60 - abs($minute % 60); |
|
| 402 | 7 | if ($minute == 60) { |
|
| 403 | 7 | $minute = 0; |
|
| 404 | } |
||
| 405 | 15 | } elseif ($minute >= 60) { |
|
| 406 | 3 | $hour += floor($minute / 60); |
|
| 407 | 3 | $minute = $minute % 60; |
|
| 408 | } |
||
| 409 | |||
| 410 | 22 | if ($hour > 23) { |
|
| 411 | 1 | $hour = $hour % 24; |
|
| 412 | 21 | } elseif ($hour < 0) { |
|
| 413 | 2 | return Functions::NAN(); |
|
| 414 | } |
||
| 415 | |||
| 416 | // Execute function |
||
| 417 | 20 | switch (Functions::getReturnDateType()) { |
|
| 418 | 20 | case Functions::RETURNDATE_EXCEL: |
|
| 419 | 19 | $date = 0; |
|
| 420 | 19 | $calendar = \PhpOffice\PhpSpreadsheet\Shared\Date::getExcelCalendar(); |
|
| 421 | 19 | if ($calendar != \PhpOffice\PhpSpreadsheet\Shared\Date::CALENDAR_WINDOWS_1900) { |
|
| 422 | $date = 1; |
||
| 423 | } |
||
| 424 | |||
| 425 | 19 | return (float) \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel($calendar, 1, $date, $hour, $minute, $second); |
|
| 426 | 1 | case Functions::RETURNDATE_PHP_NUMERIC: |
|
| 427 | return (integer) \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp(\PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel(1970, 1, 1, $hour, $minute, $second)); // -2147468400; // -2147472000 + 3600 |
||
| 428 | 1 | case Functions::RETURNDATE_PHP_OBJECT: |
|
| 429 | 1 | $dayAdjust = 0; |
|
| 430 | 1 | View Code Duplication | if ($hour < 0) { |
| 431 | $dayAdjust = floor($hour / 24); |
||
| 432 | $hour = 24 - abs($hour % 24); |
||
| 433 | if ($hour == 24) { |
||
| 434 | $hour = 0; |
||
| 435 | } |
||
| 436 | 1 | } elseif ($hour >= 24) { |
|
| 437 | $dayAdjust = floor($hour / 24); |
||
| 438 | $hour = $hour % 24; |
||
| 439 | } |
||
| 440 | 1 | $phpDateObject = new \DateTime('1900-01-01 ' . $hour . ':' . $minute . ':' . $second); |
|
| 441 | 1 | if ($dayAdjust != 0) { |
|
| 442 | $phpDateObject->modify($dayAdjust . ' days'); |
||
| 443 | } |
||
| 444 | |||
| 445 | 1 | return $phpDateObject; |
|
| 446 | } |
||
| 447 | } |
||
| 448 | |||
| 449 | /** |
||
| 450 | * DATEVALUE |
||
| 451 | * |
||
| 452 | * Returns a value that represents a particular date. |
||
| 453 | * Use DATEVALUE to convert a date represented by a text string to an Excel or PHP date/time stamp |
||
| 454 | * value. |
||
| 455 | * |
||
| 456 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date |
||
| 457 | * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 458 | * |
||
| 459 | * Excel Function: |
||
| 460 | * DATEVALUE(dateValue) |
||
| 461 | * |
||
| 462 | * @category Date/Time Functions |
||
| 463 | * @param string $dateValue Text that represents a date in a Microsoft Excel date format. |
||
| 464 | * For example, "1/30/2008" or "30-Jan-2008" are text strings within |
||
| 465 | * quotation marks that represent dates. Using the default date |
||
| 466 | * system in Excel for Windows, date_text must represent a date from |
||
| 467 | * January 1, 1900, to December 31, 9999. Using the default date |
||
| 468 | * system in Excel for the Macintosh, date_text must represent a date |
||
| 469 | * from January 1, 1904, to December 31, 9999. DATEVALUE returns the |
||
| 470 | * #VALUE! error value if date_text is out of this range. |
||
| 471 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 472 | * depending on the value of the ReturnDateType flag |
||
| 473 | */ |
||
| 474 | 236 | public static function DATEVALUE($dateValue = 1) |
|
| 475 | { |
||
| 476 | 236 | $dateValueOrig = $dateValue; |
|
| 477 | 236 | $dateValue = trim(Functions::flattenSingleValue($dateValue), '"'); |
|
| 478 | // Strip any ordinals because they're allowed in Excel (English only) |
||
| 479 | 236 | $dateValue = preg_replace('/(\d)(st|nd|rd|th)([ -\/])/Ui', '$1$3', $dateValue); |
|
| 480 | // Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany) |
||
| 481 | 236 | $dateValue = str_replace(['/', '.', '-', ' '], [' ', ' ', ' ', ' '], $dateValue); |
|
| 482 | |||
| 483 | 236 | $yearFound = false; |
|
| 484 | 236 | $t1 = explode(' ', $dateValue); |
|
| 485 | 236 | foreach ($t1 as &$t) { |
|
| 486 | 236 | if ((is_numeric($t)) && ($t > 31)) { |
|
| 487 | 205 | if ($yearFound) { |
|
| 488 | return Functions::VALUE(); |
||
| 489 | } else { |
||
| 490 | 205 | if ($t < 100) { |
|
| 491 | 2 | $t += 1900; |
|
| 492 | } |
||
| 493 | 236 | $yearFound = true; |
|
| 494 | } |
||
| 495 | } |
||
| 496 | } |
||
| 497 | 236 | if ((count($t1) == 1) && (strpos($t, ':') != false)) { |
|
| 498 | // We've been fed a time value without any date |
||
| 499 | 1 | return 0.0; |
|
| 500 | 235 | } elseif (count($t1) == 2) { |
|
| 501 | // We only have two parts of the date: either day/month or month/year |
||
| 502 | 27 | if ($yearFound) { |
|
| 503 | 3 | array_unshift($t1, 1); |
|
| 504 | } else { |
||
| 505 | 24 | if ($t1[1] > 29) { |
|
| 506 | 1 | $t1[1] += 1900; |
|
| 507 | 1 | array_unshift($t1, 1); |
|
| 508 | } else { |
||
| 509 | 23 | array_push($t1, date('Y')); |
|
| 510 | } |
||
| 511 | } |
||
| 512 | } |
||
| 513 | 235 | unset($t); |
|
| 514 | 235 | $dateValue = implode(' ', $t1); |
|
| 515 | |||
| 516 | 235 | $PHPDateArray = date_parse($dateValue); |
|
| 517 | 235 | if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { |
|
| 518 | 132 | $testVal1 = strtok($dateValue, '- '); |
|
| 519 | 132 | if ($testVal1 !== false) { |
|
| 520 | 131 | $testVal2 = strtok('- '); |
|
| 521 | 131 | if ($testVal2 !== false) { |
|
| 522 | 119 | $testVal3 = strtok('- '); |
|
| 523 | 119 | if ($testVal3 === false) { |
|
| 524 | 119 | $testVal3 = strftime('%Y'); |
|
| 525 | } |
||
| 526 | } else { |
||
| 527 | 131 | return Functions::VALUE(); |
|
| 528 | } |
||
| 529 | } else { |
||
| 530 | 1 | return Functions::VALUE(); |
|
| 531 | } |
||
| 532 | 119 | if ($testVal1 < 31 && $testVal2 < 12 && $testVal3 < 12 && strlen($testVal3) == 2) { |
|
| 533 | 1 | $testVal3 += 2000; |
|
| 534 | } |
||
| 535 | 119 | $PHPDateArray = date_parse($testVal1 . '-' . $testVal2 . '-' . $testVal3); |
|
| 536 | 119 | if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { |
|
| 537 | 25 | $PHPDateArray = date_parse($testVal2 . '-' . $testVal1 . '-' . $testVal3); |
|
| 538 | 25 | if (($PHPDateArray === false) || ($PHPDateArray['error_count'] > 0)) { |
|
| 539 | 22 | return Functions::VALUE(); |
|
| 540 | } |
||
| 541 | } |
||
| 542 | } |
||
| 543 | |||
| 544 | 206 | if (($PHPDateArray !== false) && ($PHPDateArray['error_count'] == 0)) { |
|
| 545 | // Execute function |
||
| 546 | 206 | if ($PHPDateArray['year'] == '') { |
|
| 547 | $PHPDateArray['year'] = strftime('%Y'); |
||
| 548 | } |
||
| 549 | 206 | if ($PHPDateArray['year'] < 1900) { |
|
| 550 | 2 | return Functions::VALUE(); |
|
| 551 | } |
||
| 552 | 204 | if ($PHPDateArray['month'] == '') { |
|
| 553 | $PHPDateArray['month'] = strftime('%m'); |
||
| 554 | } |
||
| 555 | 204 | if ($PHPDateArray['day'] == '') { |
|
| 556 | $PHPDateArray['day'] = strftime('%d'); |
||
| 557 | } |
||
| 558 | 204 | if (!checkdate($PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['year'])) { |
|
| 559 | 3 | return Functions::VALUE(); |
|
| 560 | } |
||
| 561 | 201 | $excelDateValue = floor( |
|
| 562 | 201 | \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel( |
|
| 563 | 201 | $PHPDateArray['year'], |
|
| 564 | 201 | $PHPDateArray['month'], |
|
| 565 | 201 | $PHPDateArray['day'], |
|
| 566 | 201 | $PHPDateArray['hour'], |
|
| 567 | 201 | $PHPDateArray['minute'], |
|
| 568 | 201 | $PHPDateArray['second'] |
|
| 569 | ) |
||
| 570 | ); |
||
| 571 | 201 | View Code Duplication | switch (Functions::getReturnDateType()) { |
| 572 | 201 | case Functions::RETURNDATE_EXCEL: |
|
| 573 | 199 | return (float) $excelDateValue; |
|
| 574 | 2 | case Functions::RETURNDATE_PHP_NUMERIC: |
|
| 575 | 1 | return (integer) \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($excelDateValue); |
|
| 576 | 1 | case Functions::RETURNDATE_PHP_OBJECT: |
|
| 577 | 1 | return new \DateTime($PHPDateArray['year'] . '-' . $PHPDateArray['month'] . '-' . $PHPDateArray['day'] . ' 00:00:00'); |
|
| 578 | } |
||
| 579 | } |
||
| 580 | |||
| 581 | return Functions::VALUE(); |
||
| 582 | } |
||
| 583 | |||
| 584 | /** |
||
| 585 | * TIMEVALUE |
||
| 586 | * |
||
| 587 | * Returns a value that represents a particular time. |
||
| 588 | * Use TIMEVALUE to convert a time represented by a text string to an Excel or PHP date/time stamp |
||
| 589 | * value. |
||
| 590 | * |
||
| 591 | * NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the time |
||
| 592 | * format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
||
| 593 | * |
||
| 594 | * Excel Function: |
||
| 595 | * TIMEVALUE(timeValue) |
||
| 596 | * |
||
| 597 | * @category Date/Time Functions |
||
| 598 | * @param string $timeValue A text string that represents a time in any one of the Microsoft |
||
| 599 | * Excel time formats; for example, "6:45 PM" and "18:45" text strings |
||
| 600 | * within quotation marks that represent time. |
||
| 601 | * Date information in time_text is ignored. |
||
| 602 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 603 | * depending on the value of the ReturnDateType flag |
||
| 604 | */ |
||
| 605 | 21 | public static function TIMEVALUE($timeValue) |
|
| 606 | { |
||
| 607 | 21 | $timeValue = trim(Functions::flattenSingleValue($timeValue), '"'); |
|
| 608 | 21 | $timeValue = str_replace(['/', '.'], ['-', '-'], $timeValue); |
|
| 609 | |||
| 610 | 21 | $arraySplit = preg_split('/[\/:\-\s]/', $timeValue); |
|
| 611 | 21 | if ((count($arraySplit) == 2 || count($arraySplit) == 3) && $arraySplit[0] > 24) { |
|
| 612 | 1 | $arraySplit[0] = ($arraySplit[0] % 24); |
|
| 613 | 1 | $timeValue = implode(':', $arraySplit); |
|
| 614 | } |
||
| 615 | |||
| 616 | 21 | $PHPDateArray = date_parse($timeValue); |
|
| 617 | 21 | if (($PHPDateArray !== false) && ($PHPDateArray['error_count'] == 0)) { |
|
| 618 | 18 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { |
|
| 619 | $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel( |
||
| 620 | $PHPDateArray['year'], |
||
| 621 | $PHPDateArray['month'], |
||
| 622 | $PHPDateArray['day'], |
||
| 623 | $PHPDateArray['hour'], |
||
| 624 | $PHPDateArray['minute'], |
||
| 625 | $PHPDateArray['second'] |
||
| 626 | ); |
||
| 627 | } else { |
||
| 628 | 18 | $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1; |
|
| 629 | } |
||
| 630 | |||
| 631 | 18 | View Code Duplication | switch (Functions::getReturnDateType()) { |
| 632 | 18 | case Functions::RETURNDATE_EXCEL: |
|
| 633 | 17 | return (float) $excelDateValue; |
|
| 634 | 1 | case Functions::RETURNDATE_PHP_NUMERIC: |
|
| 635 | return (integer) $phpDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($excelDateValue + 25569) - 3600; |
||
| 636 | 1 | case Functions::RETURNDATE_PHP_OBJECT: |
|
| 637 | 1 | return new \DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']); |
|
| 638 | } |
||
| 639 | } |
||
| 640 | |||
| 641 | 3 | return Functions::VALUE(); |
|
| 642 | } |
||
| 643 | |||
| 644 | /** |
||
| 645 | * DATEDIF |
||
| 646 | * |
||
| 647 | * @param mixed $startDate Excel date serial value, PHP date/time stamp, PHP DateTime object |
||
| 648 | * or a standard date string |
||
| 649 | * @param mixed $endDate Excel date serial value, PHP date/time stamp, PHP DateTime object |
||
| 650 | * or a standard date string |
||
| 651 | * @param string $unit |
||
| 652 | * @return int Interval between the dates |
||
| 653 | */ |
||
| 654 | 10 | public static function DATEDIF($startDate = 0, $endDate = 0, $unit = 'D') |
|
| 655 | { |
||
| 656 | 10 | $startDate = Functions::flattenSingleValue($startDate); |
|
| 657 | 10 | $endDate = Functions::flattenSingleValue($endDate); |
|
| 658 | 10 | $unit = strtoupper(Functions::flattenSingleValue($unit)); |
|
| 659 | |||
| 660 | 10 | if (is_string($startDate = self::getDateValue($startDate))) { |
|
| 661 | return Functions::VALUE(); |
||
| 662 | } |
||
| 663 | 10 | if (is_string($endDate = self::getDateValue($endDate))) { |
|
| 664 | return Functions::VALUE(); |
||
| 665 | } |
||
| 666 | |||
| 667 | // Validate parameters |
||
| 668 | 10 | if ($startDate >= $endDate) { |
|
| 669 | return Functions::NAN(); |
||
| 670 | } |
||
| 671 | |||
| 672 | // Execute function |
||
| 673 | 10 | $difference = $endDate - $startDate; |
|
| 674 | |||
| 675 | 10 | $PHPStartDateObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($startDate); |
|
| 676 | 10 | $startDays = $PHPStartDateObject->format('j'); |
|
| 677 | 10 | $startMonths = $PHPStartDateObject->format('n'); |
|
| 678 | 10 | $startYears = $PHPStartDateObject->format('Y'); |
|
| 679 | |||
| 680 | 10 | $PHPEndDateObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($endDate); |
|
| 681 | 10 | $endDays = $PHPEndDateObject->format('j'); |
|
| 682 | 10 | $endMonths = $PHPEndDateObject->format('n'); |
|
| 683 | 10 | $endYears = $PHPEndDateObject->format('Y'); |
|
| 684 | |||
| 685 | 10 | $retVal = Functions::NAN(); |
|
| 686 | switch ($unit) { |
||
| 687 | 10 | case 'D': |
|
| 688 | 10 | $retVal = intval($difference); |
|
| 689 | 10 | break; |
|
| 690 | View Code Duplication | case 'M': |
|
| 691 | $retVal = intval($endMonths - $startMonths) + (intval($endYears - $startYears) * 12); |
||
| 692 | // We're only interested in full months |
||
| 693 | if ($endDays < $startDays) { |
||
| 694 | --$retVal; |
||
| 695 | } |
||
| 696 | break; |
||
| 697 | case 'Y': |
||
| 698 | $retVal = intval($endYears - $startYears); |
||
| 699 | // We're only interested in full months |
||
| 700 | if ($endMonths < $startMonths) { |
||
| 701 | --$retVal; |
||
| 702 | } elseif (($endMonths == $startMonths) && ($endDays < $startDays)) { |
||
| 703 | // Remove start month |
||
| 704 | --$retVal; |
||
| 705 | // Remove end month |
||
| 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 | $retVal += ($adjustDays - $startDays); |
||
| 715 | } else { |
||
| 716 | $retVal = $endDays - $startDays; |
||
| 717 | } |
||
| 718 | break; |
||
| 719 | View Code Duplication | case 'YM': |
|
| 720 | $retVal = intval($endMonths - $startMonths); |
||
| 721 | if ($retVal < 0) { |
||
| 722 | $retVal += 12; |
||
| 723 | } |
||
| 724 | // We're only interested in full months |
||
| 725 | if ($endDays < $startDays) { |
||
| 726 | --$retVal; |
||
| 727 | } |
||
| 728 | break; |
||
| 729 | case 'YD': |
||
| 730 | $retVal = intval($difference); |
||
| 731 | if ($endYears > $startYears) { |
||
| 732 | while ($endYears > $startYears) { |
||
| 733 | $PHPEndDateObject->modify('-1 year'); |
||
| 734 | $endYears = $PHPEndDateObject->format('Y'); |
||
| 735 | } |
||
| 736 | $retVal = $PHPEndDateObject->format('z') - $PHPStartDateObject->format('z'); |
||
| 737 | if ($retVal < 0) { |
||
| 738 | $retVal += 365; |
||
| 739 | } |
||
| 740 | } |
||
| 741 | break; |
||
| 742 | default: |
||
| 743 | $retVal = Functions::VALUE(); |
||
| 744 | } |
||
| 745 | |||
| 746 | 10 | return $retVal; |
|
| 747 | } |
||
| 748 | |||
| 749 | /** |
||
| 750 | * DAYS360 |
||
| 751 | * |
||
| 752 | * Returns the number of days between two dates based on a 360-day year (twelve 30-day months), |
||
| 753 | * which is used in some accounting calculations. Use this function to help compute payments if |
||
| 754 | * your accounting system is based on twelve 30-day months. |
||
| 755 | * |
||
| 756 | * Excel Function: |
||
| 757 | * DAYS360(startDate,endDate[,method]) |
||
| 758 | * |
||
| 759 | * @category Date/Time Functions |
||
| 760 | * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 761 | * PHP DateTime object, or a standard date string |
||
| 762 | * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 763 | * PHP DateTime object, or a standard date string |
||
| 764 | * @param bool $method US or European Method |
||
| 765 | * FALSE or omitted: U.S. (NASD) method. If the starting date is |
||
| 766 | * the last day of a month, it becomes equal to the 30th of the |
||
| 767 | * same month. If the ending date is the last day of a month and |
||
| 768 | * the starting date is earlier than the 30th of a month, the |
||
| 769 | * ending date becomes equal to the 1st of the next month; |
||
| 770 | * otherwise the ending date becomes equal to the 30th of the |
||
| 771 | * same month. |
||
| 772 | * TRUE: European method. Starting dates and ending dates that |
||
| 773 | * occur on the 31st of a month become equal to the 30th of the |
||
| 774 | * same month. |
||
| 775 | * @return int Number of days between start date and end date |
||
| 776 | */ |
||
| 777 | 46 | public static function DAYS360($startDate = 0, $endDate = 0, $method = false) |
|
| 778 | { |
||
| 779 | 46 | $startDate = Functions::flattenSingleValue($startDate); |
|
| 780 | 46 | $endDate = Functions::flattenSingleValue($endDate); |
|
| 781 | |||
| 782 | 46 | if (is_string($startDate = self::getDateValue($startDate))) { |
|
| 783 | 1 | return Functions::VALUE(); |
|
| 784 | } |
||
| 785 | 45 | if (is_string($endDate = self::getDateValue($endDate))) { |
|
| 786 | 1 | return Functions::VALUE(); |
|
| 787 | } |
||
| 788 | |||
| 789 | 44 | if (!is_bool($method)) { |
|
| 790 | 2 | return Functions::VALUE(); |
|
| 791 | } |
||
| 792 | |||
| 793 | // Execute function |
||
| 794 | 42 | $PHPStartDateObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($startDate); |
|
| 795 | 42 | $startDay = $PHPStartDateObject->format('j'); |
|
| 796 | 42 | $startMonth = $PHPStartDateObject->format('n'); |
|
| 797 | 42 | $startYear = $PHPStartDateObject->format('Y'); |
|
| 798 | |||
| 799 | 42 | $PHPEndDateObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($endDate); |
|
| 800 | 42 | $endDay = $PHPEndDateObject->format('j'); |
|
| 801 | 42 | $endMonth = $PHPEndDateObject->format('n'); |
|
| 802 | 42 | $endYear = $PHPEndDateObject->format('Y'); |
|
| 803 | |||
| 804 | 42 | return self::dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, !$method); |
|
| 805 | } |
||
| 806 | |||
| 807 | /** |
||
| 808 | * YEARFRAC |
||
| 809 | * |
||
| 810 | * Calculates the fraction of the year represented by the number of whole days between two dates |
||
| 811 | * (the start_date and the end_date). |
||
| 812 | * Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or |
||
| 813 | * obligations to assign to a specific term. |
||
| 814 | * |
||
| 815 | * Excel Function: |
||
| 816 | * YEARFRAC(startDate,endDate[,method]) |
||
| 817 | * |
||
| 818 | * @category Date/Time Functions |
||
| 819 | * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 820 | * PHP DateTime object, or a standard date string |
||
| 821 | * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 822 | * PHP DateTime object, or a standard date string |
||
| 823 | * @param int $method Method used for the calculation |
||
| 824 | * 0 or omitted US (NASD) 30/360 |
||
| 825 | * 1 Actual/actual |
||
| 826 | * 2 Actual/360 |
||
| 827 | * 3 Actual/365 |
||
| 828 | * 4 European 30/360 |
||
| 829 | * @return float fraction of the year |
||
| 830 | */ |
||
| 831 | 25 | public static function YEARFRAC($startDate = 0, $endDate = 0, $method = 0) |
|
| 904 | |||
| 905 | /** |
||
| 906 | * NETWORKDAYS |
||
| 907 | * |
||
| 908 | * Returns the number of whole working days between start_date and end_date. Working days |
||
| 909 | * exclude weekends and any dates identified in holidays. |
||
| 910 | * Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days |
||
| 911 | * worked during a specific term. |
||
| 912 | * |
||
| 913 | * Excel Function: |
||
| 914 | * NETWORKDAYS(startDate,endDate[,holidays[,holiday[,...]]]) |
||
| 915 | * |
||
| 916 | * @category Date/Time Functions |
||
| 917 | * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 918 | * PHP DateTime object, or a standard date string |
||
| 919 | * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 920 | * PHP DateTime object, or a standard date string |
||
| 921 | * @return int Interval between the dates |
||
| 922 | */ |
||
| 923 | 18 | public static function NETWORKDAYS($startDate, $endDate) |
|
| 984 | |||
| 985 | /** |
||
| 986 | * WORKDAY |
||
| 987 | * |
||
| 988 | * Returns the date that is the indicated number of working days before or after a date (the |
||
| 989 | * starting date). Working days exclude weekends and any dates identified as holidays. |
||
| 990 | * Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected |
||
| 991 | * delivery times, or the number of days of work performed. |
||
| 992 | * |
||
| 993 | * Excel Function: |
||
| 994 | * WORKDAY(startDate,endDays[,holidays[,holiday[,...]]]) |
||
| 995 | * |
||
| 996 | * @category Date/Time Functions |
||
| 997 | * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer), |
||
| 998 | * PHP DateTime object, or a standard date string |
||
| 999 | * @param int $endDays The number of nonweekend and nonholiday days before or after |
||
| 1000 | * startDate. A positive value for days yields a future date; a |
||
| 1001 | * negative value yields a past date. |
||
| 1002 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 1003 | * depending on the value of the ReturnDateType flag |
||
| 1004 | */ |
||
| 1005 | 13 | public static function WORKDAY($startDate, $endDays) |
|
| 1006 | { |
||
| 1007 | // Retrieve the mandatory start date and days that are referenced in the function definition |
||
| 1008 | 13 | $startDate = Functions::flattenSingleValue($startDate); |
|
| 1009 | 13 | $endDays = Functions::flattenSingleValue($endDays); |
|
| 1010 | // Flush the mandatory start date and days that are referenced in the function definition, and get the optional days |
||
| 1011 | 13 | $dateArgs = Functions::flattenArray(func_get_args()); |
|
| 1012 | 13 | array_shift($dateArgs); |
|
| 1013 | 13 | array_shift($dateArgs); |
|
| 1014 | |||
| 1015 | 13 | if ((is_string($startDate = self::getDateValue($startDate))) || (!is_numeric($endDays))) { |
|
| 1016 | 1 | return Functions::VALUE(); |
|
| 1017 | } |
||
| 1018 | 12 | $startDate = (float) floor($startDate); |
|
| 1019 | 12 | $endDays = (int) floor($endDays); |
|
| 1020 | // If endDays is 0, we always return startDate |
||
| 1021 | 12 | if ($endDays == 0) { |
|
| 1022 | return $startDate; |
||
| 1023 | } |
||
| 1024 | |||
| 1025 | 12 | $decrementing = ($endDays < 0) ? true : false; |
|
| 1026 | |||
| 1027 | // Adjust the start date if it falls over a weekend |
||
| 1028 | |||
| 1029 | 12 | $startDoW = self::DAYOFWEEK($startDate, 3); |
|
| 1030 | 12 | if (self::DAYOFWEEK($startDate, 3) >= 5) { |
|
| 1031 | 4 | $startDate += ($decrementing) ? -$startDoW + 4 : 7 - $startDoW; |
|
| 1032 | 4 | ($decrementing) ? $endDays++ : $endDays--; |
|
| 1033 | } |
||
| 1034 | |||
| 1035 | // Add endDays |
||
| 1036 | 12 | $endDate = (float) $startDate + (intval($endDays / 5) * 7) + ($endDays % 5); |
|
| 1037 | |||
| 1038 | // Adjust the calculated end date if it falls over a weekend |
||
| 1039 | 12 | $endDoW = self::DAYOFWEEK($endDate, 3); |
|
| 1040 | 12 | if ($endDoW >= 5) { |
|
| 1041 | 1 | $endDate += ($decrementing) ? -$endDoW + 4 : 7 - $endDoW; |
|
| 1042 | } |
||
| 1043 | |||
| 1044 | // Test any extra holiday parameters |
||
| 1045 | 12 | if (!empty($dateArgs)) { |
|
| 1046 | 4 | $holidayCountedArray = $holidayDates = []; |
|
| 1047 | 4 | foreach ($dateArgs as $holidayDate) { |
|
| 1048 | 4 | if (($holidayDate !== null) && (trim($holidayDate) > '')) { |
|
| 1049 | 4 | if (is_string($holidayDate = self::getDateValue($holidayDate))) { |
|
| 1050 | return Functions::VALUE(); |
||
| 1051 | } |
||
| 1052 | 4 | if (self::DAYOFWEEK($holidayDate, 3) < 5) { |
|
| 1053 | 4 | $holidayDates[] = $holidayDate; |
|
| 1054 | } |
||
| 1055 | } |
||
| 1056 | } |
||
| 1057 | 4 | if ($decrementing) { |
|
| 1058 | 1 | rsort($holidayDates, SORT_NUMERIC); |
|
| 1059 | } else { |
||
| 1060 | 3 | sort($holidayDates, SORT_NUMERIC); |
|
| 1061 | } |
||
| 1062 | 4 | foreach ($holidayDates as $holidayDate) { |
|
| 1063 | 4 | if ($decrementing) { |
|
| 1064 | 1 | View Code Duplication | if (($holidayDate <= $startDate) && ($holidayDate >= $endDate)) { |
| 1065 | 1 | if (!in_array($holidayDate, $holidayCountedArray)) { |
|
| 1066 | 1 | --$endDate; |
|
| 1067 | 1 | $holidayCountedArray[] = $holidayDate; |
|
| 1068 | } |
||
| 1069 | } |
||
| 1070 | View Code Duplication | } else { |
|
| 1071 | 3 | if (($holidayDate >= $startDate) && ($holidayDate <= $endDate)) { |
|
| 1072 | 3 | if (!in_array($holidayDate, $holidayCountedArray)) { |
|
| 1073 | 3 | ++$endDate; |
|
| 1074 | 3 | $holidayCountedArray[] = $holidayDate; |
|
| 1075 | } |
||
| 1076 | } |
||
| 1077 | } |
||
| 1078 | // Adjust the calculated end date if it falls over a weekend |
||
| 1079 | 4 | $endDoW = self::DAYOFWEEK($endDate, 3); |
|
| 1080 | 4 | if ($endDoW >= 5) { |
|
| 1081 | 4 | $endDate += ($decrementing) ? -$endDoW + 4 : 7 - $endDoW; |
|
| 1082 | } |
||
| 1083 | } |
||
| 1084 | } |
||
| 1085 | |||
| 1086 | 12 | View Code Duplication | switch (Functions::getReturnDateType()) { |
| 1087 | 12 | case Functions::RETURNDATE_EXCEL: |
|
| 1088 | 12 | return (float) $endDate; |
|
| 1089 | case Functions::RETURNDATE_PHP_NUMERIC: |
||
| 1090 | return (integer) \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($endDate); |
||
| 1091 | case Functions::RETURNDATE_PHP_OBJECT: |
||
| 1092 | return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($endDate); |
||
| 1093 | } |
||
| 1094 | } |
||
| 1095 | |||
| 1096 | /** |
||
| 1097 | * DAYOFMONTH |
||
| 1098 | * |
||
| 1099 | * Returns the day of the month, for a specified date. The day is given as an integer |
||
| 1100 | * ranging from 1 to 31. |
||
| 1101 | * |
||
| 1102 | * Excel Function: |
||
| 1103 | * DAY(dateValue) |
||
| 1104 | * |
||
| 1105 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1106 | * PHP DateTime object, or a standard date string |
||
| 1107 | * @return int Day of the month |
||
| 1108 | */ |
||
| 1109 | 13 | public static function DAYOFMONTH($dateValue = 1) |
|
| 1128 | |||
| 1129 | /** |
||
| 1130 | * DAYOFWEEK |
||
| 1131 | * |
||
| 1132 | * Returns the day of the week for a specified date. The day is given as an integer |
||
| 1133 | * ranging from 0 to 7 (dependent on the requested style). |
||
| 1134 | * |
||
| 1135 | * Excel Function: |
||
| 1136 | * WEEKDAY(dateValue[,style]) |
||
| 1137 | * |
||
| 1138 | * @param int $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1139 | * PHP DateTime object, or a standard date string |
||
| 1140 | * @param int $style A number that determines the type of return value |
||
| 1141 | * 1 or omitted Numbers 1 (Sunday) through 7 (Saturday). |
||
| 1142 | * 2 Numbers 1 (Monday) through 7 (Sunday). |
||
| 1143 | * 3 Numbers 0 (Monday) through 6 (Sunday). |
||
| 1144 | * @return int Day of the week value |
||
| 1145 | */ |
||
| 1146 | 30 | public static function DAYOFWEEK($dateValue = 1, $style = 1) |
|
| 1200 | |||
| 1201 | /** |
||
| 1202 | * WEEKNUM |
||
| 1203 | * |
||
| 1204 | * Returns the week of the year for a specified date. |
||
| 1205 | * The WEEKNUM function considers the week containing January 1 to be the first week of the year. |
||
| 1206 | * However, there is a European standard that defines the first week as the one with the majority |
||
| 1207 | * of days (four or more) falling in the new year. This means that for years in which there are |
||
| 1208 | * three days or less in the first week of January, the WEEKNUM function returns week numbers |
||
| 1209 | * that are incorrect according to the European standard. |
||
| 1210 | * |
||
| 1211 | * Excel Function: |
||
| 1212 | * WEEKNUM(dateValue[,style]) |
||
| 1213 | * |
||
| 1214 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1215 | * PHP DateTime object, or a standard date string |
||
| 1216 | * @param int $method Week begins on Sunday or Monday |
||
| 1217 | * 1 or omitted Week begins on Sunday. |
||
| 1218 | * 2 Week begins on Monday. |
||
| 1219 | * @return int Week Number |
||
| 1220 | */ |
||
| 1221 | 15 | public static function WEEKNUM($dateValue = 1, $method = 1) |
|
| 1256 | |||
| 1257 | /** |
||
| 1258 | * MONTHOFYEAR |
||
| 1259 | * |
||
| 1260 | * Returns the month of a date represented by a serial number. |
||
| 1261 | * The month is given as an integer, ranging from 1 (January) to 12 (December). |
||
| 1262 | * |
||
| 1263 | * Excel Function: |
||
| 1264 | * MONTH(dateValue) |
||
| 1265 | * |
||
| 1266 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1267 | * PHP DateTime object, or a standard date string |
||
| 1268 | * @return int Month of the year |
||
| 1269 | */ |
||
| 1270 | 17 | View Code Duplication | public static function MONTHOFYEAR($dateValue = 1) |
| 1288 | |||
| 1289 | /** |
||
| 1290 | * YEAR |
||
| 1291 | * |
||
| 1292 | * Returns the year corresponding to a date. |
||
| 1293 | * The year is returned as an integer in the range 1900-9999. |
||
| 1294 | * |
||
| 1295 | * Excel Function: |
||
| 1296 | * YEAR(dateValue) |
||
| 1297 | * |
||
| 1298 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1299 | * PHP DateTime object, or a standard date string |
||
| 1300 | * @return int Year |
||
| 1301 | */ |
||
| 1302 | 21 | View Code Duplication | public static function YEAR($dateValue = 1) |
| 1319 | |||
| 1320 | /** |
||
| 1321 | * HOUROFDAY |
||
| 1322 | * |
||
| 1323 | * Returns the hour of a time value. |
||
| 1324 | * The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
||
| 1325 | * |
||
| 1326 | * Excel Function: |
||
| 1327 | * HOUR(timeValue) |
||
| 1328 | * |
||
| 1329 | * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1330 | * PHP DateTime object, or a standard time string |
||
| 1331 | * @return int Hour |
||
| 1332 | */ |
||
| 1333 | 12 | View Code Duplication | public static function HOUROFDAY($timeValue = 0) |
| 1359 | |||
| 1360 | /** |
||
| 1361 | * MINUTEOFHOUR |
||
| 1362 | * |
||
| 1363 | * Returns the minutes of a time value. |
||
| 1364 | * The minute is given as an integer, ranging from 0 to 59. |
||
| 1365 | * |
||
| 1366 | * Excel Function: |
||
| 1367 | * MINUTE(timeValue) |
||
| 1368 | * |
||
| 1369 | * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1370 | * PHP DateTime object, or a standard time string |
||
| 1371 | * @return int Minute |
||
| 1372 | */ |
||
| 1373 | View Code Duplication | public static function MINUTEOFHOUR($timeValue = 0) |
|
| 1399 | |||
| 1400 | /** |
||
| 1401 | * SECONDOFMINUTE |
||
| 1402 | * |
||
| 1403 | * Returns the seconds of a time value. |
||
| 1404 | * The second is given as an integer in the range 0 (zero) to 59. |
||
| 1405 | * |
||
| 1406 | * Excel Function: |
||
| 1407 | * SECOND(timeValue) |
||
| 1408 | * |
||
| 1409 | * @param mixed $timeValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1410 | * PHP DateTime object, or a standard time string |
||
| 1411 | * @return int Second |
||
| 1412 | */ |
||
| 1413 | View Code Duplication | public static function SECONDOFMINUTE($timeValue = 0) |
|
| 1439 | |||
| 1440 | /** |
||
| 1441 | * EDATE |
||
| 1442 | * |
||
| 1443 | * Returns the serial number that represents the date that is the indicated number of months |
||
| 1444 | * before or after a specified date (the start_date). |
||
| 1445 | * Use EDATE to calculate maturity dates or due dates that fall on the same day of the month |
||
| 1446 | * as the date of issue. |
||
| 1447 | * |
||
| 1448 | * Excel Function: |
||
| 1449 | * EDATE(dateValue,adjustmentMonths) |
||
| 1450 | * |
||
| 1451 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1452 | * PHP DateTime object, or a standard date string |
||
| 1453 | * @param int $adjustmentMonths The number of months before or after start_date. |
||
| 1454 | * A positive value for months yields a future date; |
||
| 1455 | * a negative value yields a past date. |
||
| 1456 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 1457 | * depending on the value of the ReturnDateType flag |
||
| 1458 | */ |
||
| 1459 | 17 | public static function EDATE($dateValue = 1, $adjustmentMonths = 0) |
|
| 1485 | |||
| 1486 | /** |
||
| 1487 | * EOMONTH |
||
| 1488 | * |
||
| 1489 | * Returns the date value for the last day of the month that is the indicated number of months |
||
| 1490 | * before or after start_date. |
||
| 1491 | * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
||
| 1492 | * |
||
| 1493 | * Excel Function: |
||
| 1494 | * EOMONTH(dateValue,adjustmentMonths) |
||
| 1495 | * |
||
| 1496 | * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer), |
||
| 1497 | * PHP DateTime object, or a standard date string |
||
| 1498 | * @param int $adjustmentMonths The number of months before or after start_date. |
||
| 1499 | * A positive value for months yields a future date; |
||
| 1500 | * a negative value yields a past date. |
||
| 1501 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 1502 | * depending on the value of the ReturnDateType flag |
||
| 1503 | */ |
||
| 1504 | 19 | public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) |
|
| 1533 | } |
||
| 1534 |
Loading history...
If a method or function can return multiple different values and unless you are sure that you only can receive a single value in this context, we recommend to add an additional type check:
If this a common case that PHP Analyzer should handle natively, please let us know by opening an issue.