ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
DateTime.php
Go to the documentation of this file.
1<?php
30if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36}
37
38
47
54 public static function _isLeapYear($year) {
55 return ((($year % 4) == 0) && (($year % 100) != 0) || (($year % 400) == 0));
56 } // function _isLeapYear()
57
58
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
101 public static function _getDateValue($dateValue) {
102 if (!is_numeric($dateValue)) {
103 if ((is_string($dateValue)) &&
106 }
107 if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
108 $dateValue = PHPExcel_Shared_Date::PHPToExcel($dateValue);
109 } else {
112 $dateValue = self::DATEVALUE($dateValue);
114 }
115 }
116 return $dateValue;
117 } // function _getDateValue()
118
119
126 private static function _getTimeValue($timeValue) {
129 $timeValue = self::TIMEVALUE($timeValue);
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
180 public static function DATETIMENOW() {
181 $saveTimeZone = date_default_timezone_get();
182 date_default_timezone_set('UTC');
183 $retValue = False;
186 $retValue = (float) PHPExcel_Shared_Date::PHPToExcel(time());
187 break;
189 $retValue = (integer) time();
190 break;
192 $retValue = new DateTime();
193 break;
194 }
195 date_default_timezone_set($saveTimeZone);
196
197 return $retValue;
198 } // function DATETIMENOW()
199
200
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()));
227 $retValue = (float) $excelDateTime;
228 break;
230 $retValue = (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateTime);
231 break;
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
292 public static function DATE($year = 0, $month = 1, $day = 1) {
296
297 if (($month !== NULL) && (!is_numeric($month))) {
299 }
300
301 if (($day !== NULL) && (!is_numeric($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))) {
312 }
313 $year = (integer) $year;
314 $month = (integer) $month;
315 $day = (integer) $day;
316
318 // Validate parameters
319 if ($year < ($baseYear-1900)) {
321 }
322 if ((($baseYear-1900) != 0) && ($year < $baseYear) && ($year >= 1900)) {
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)) {
344 }
345
346 // Execute function
347 $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day);
350 return (float) $excelDateValue;
352 return (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
354 return PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
355 }
356 } // function DATE()
357
358
386 public static function TIME($hour = 0, $minute = 0, $second = 0) {
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))) {
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) {
423 }
424
425 // Execute function
428 $date = 0;
431 $date = 1;
432 }
433 return (float) PHPExcel_Shared_Date::FormattedPHPToExcel($calendar, 1, $date, $hour, $minute, $second);
435 return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::FormattedPHPToExcel(1970, 1, 1, $hour, $minute, $second)); // -2147468400; // -2147472000 + 3600
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
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) {
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 {
526 }
527 } else {
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)) {
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)
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
550 return (float) $excelDateValue;
552 return (integer) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
554 return new DateTime($PHPDateArray['year'].'-'.$PHPDateArray['month'].'-'.$PHPDateArray['day'].' 00:00:00');
555 }
556 }
558 } // function DATEVALUE()
559
560
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)) {
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
597 return (float) $excelDateValue;
599 return (integer) $phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue+25569) - 3600;;
601 return new DateTime('1900-01-01 '.$PHPDateArray['hour'].':'.$PHPDateArray['minute'].':'.$PHPDateArray['second']);
602 }
603 }
605 } // function TIMEVALUE()
606
607
618 public static function DATEDIF($startDate = 0, $endDate = 0, $unit = 'D') {
622
623 if (is_string($startDate = self::_getDateValue($startDate))) {
625 }
626 if (is_string($endDate = self::_getDateValue($endDate))) {
628 }
629
630 // Validate parameters
631 if ($startDate >= $endDate) {
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
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:
702 }
703 return $retVal;
704 } // function DATEDIF()
705
706
736 public static function DAYS360($startDate = 0, $endDate = 0, $method = false) {
739
740 if (is_string($startDate = self::_getDateValue($startDate))) {
742 }
743 if (is_string($endDate = self::_getDateValue($endDate))) {
745 }
746
747 if (!is_bool($method)) {
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
791 public static function YEARFRAC($startDate = 0, $endDate = 0, $method = 0) {
795
796 if (is_string($startDate = self::_getDateValue($startDate))) {
798 }
799 if (is_string($endDate = self::_getDateValue($endDate))) {
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 }
860 } // function YEARFRAC()
861
862
886 public static function NETWORKDAYS($startDate,$endDate) {
887 // Retrieve the mandatory start and end date that are referenced in the function definition
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))) {
898 }
899 $startDate = (float) floor($startDate);
900 if (is_string($endDate = $eDate = self::_getDateValue($endDate))) {
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))) {
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
968 public static function WORKDAY($startDate,$endDays) {
969 // Retrieve the mandatory start date and days that are referenced in the function definition
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))) {
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))) {
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
1049 return (float) $endDate;
1051 return (integer) PHPExcel_Shared_Date::ExcelToPHP($endDate);
1054 }
1055 } // function WORKDAY()
1056
1057
1071 public static function DAYOFMONTH($dateValue = 1) {
1073
1074 if ($dateValue === null) {
1075 $dateValue = 1;
1076 } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
1078 } elseif ($dateValue == 0.0) {
1079 return 0;
1080 } elseif ($dateValue < 0.0) {
1082 }
1083
1084 // Execute function
1085 $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
1086
1087 return (int) $PHPDateObject->format('j');
1088 } // function DAYOFMONTH()
1089
1090
1108 public static function DAYOFWEEK($dateValue = 1, $style = 1) {
1111
1112 if (!is_numeric($style)) {
1114 } elseif (($style < 1) || ($style > 3)) {
1116 }
1117 $style = floor($style);
1118
1119 if ($dateValue === null) {
1120 $dateValue = 1;
1121 } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
1123 } elseif ($dateValue < 0.0) {
1125 }
1126
1127 // Execute function
1128 $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
1129 $DoW = $PHPDateObject->format('w');
1130
1131 $firstDay = 1;
1132 switch ($style) {
1133 case 1: ++$DoW;
1134 break;
1135 case 2: if ($DoW == 0) { $DoW = 7; }
1136 break;
1137 case 3: if ($DoW == 0) { $DoW = 7; }
1138 $firstDay = 0;
1139 --$DoW;
1140 break;
1141 }
1143 // Test for Excel's 1900 leap year, and introduce the error as required
1144 if (($PHPDateObject->format('Y') == 1900) && ($PHPDateObject->format('n') <= 2)) {
1145 --$DoW;
1146 if ($DoW < $firstDay) {
1147 $DoW += 7;
1148 }
1149 }
1150 }
1151
1152 return (int) $DoW;
1153 } // function DAYOFWEEK()
1154
1155
1176 public static function WEEKOFYEAR($dateValue = 1, $method = 1) {
1179
1180 if (!is_numeric($method)) {
1182 } elseif (($method < 1) || ($method > 2)) {
1184 }
1185 $method = floor($method);
1186
1187 if ($dateValue === null) {
1188 $dateValue = 1;
1189 } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
1191 } elseif ($dateValue < 0.0) {
1193 }
1194
1195 // Execute function
1196 $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
1197 $dayOfYear = $PHPDateObject->format('z');
1198 $dow = $PHPDateObject->format('w');
1199 $PHPDateObject->modify('-'.$dayOfYear.' days');
1200 $dow = $PHPDateObject->format('w');
1201 $daysInFirstWeek = 7 - (($dow + (2 - $method)) % 7);
1202 $dayOfYear -= $daysInFirstWeek;
1203 $weekOfYear = ceil($dayOfYear / 7) + 1;
1204
1205 return (int) $weekOfYear;
1206 } // function WEEKOFYEAR()
1207
1208
1222 public static function MONTHOFYEAR($dateValue = 1) {
1224
1225 if ($dateValue === null) {
1226 $dateValue = 1;
1227 } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
1229 } elseif ($dateValue < 0.0) {
1231 }
1232
1233 // Execute function
1234 $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
1235
1236 return (int) $PHPDateObject->format('n');
1237 } // function MONTHOFYEAR()
1238
1239
1253 public static function YEAR($dateValue = 1) {
1255
1256 if ($dateValue === null) {
1257 $dateValue = 1;
1258 } elseif (is_string($dateValue = self::_getDateValue($dateValue))) {
1260 } elseif ($dateValue < 0.0) {
1262 }
1263
1264 // Execute function
1265 $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
1266
1267 return (int) $PHPDateObject->format('Y');
1268 } // function YEAR()
1269
1270
1284 public static function HOUROFDAY($timeValue = 0) {
1286
1287 if (!is_numeric($timeValue)) {
1289 $testVal = strtok($timeValue,'/-: ');
1290 if (strlen($testVal) < strlen($timeValue)) {
1292 }
1293 }
1294 $timeValue = self::_getTimeValue($timeValue);
1295 if (is_string($timeValue)) {
1297 }
1298 }
1299 // Execute function
1300 if ($timeValue >= 1) {
1301 $timeValue = fmod($timeValue,1);
1302 } elseif ($timeValue < 0.0) {
1304 }
1305 $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
1306
1307 return (int) gmdate('G',$timeValue);
1308 } // function HOUROFDAY()
1309
1310
1324 public static function MINUTEOFHOUR($timeValue = 0) {
1325 $timeValue = $timeTester = PHPExcel_Calculation_Functions::flattenSingleValue($timeValue);
1326
1327 if (!is_numeric($timeValue)) {
1329 $testVal = strtok($timeValue,'/-: ');
1330 if (strlen($testVal) < strlen($timeValue)) {
1332 }
1333 }
1334 $timeValue = self::_getTimeValue($timeValue);
1335 if (is_string($timeValue)) {
1337 }
1338 }
1339 // Execute function
1340 if ($timeValue >= 1) {
1341 $timeValue = fmod($timeValue,1);
1342 } elseif ($timeValue < 0.0) {
1344 }
1345 $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
1346
1347 return (int) gmdate('i',$timeValue);
1348 } // function MINUTEOFHOUR()
1349
1350
1364 public static function SECONDOFMINUTE($timeValue = 0) {
1366
1367 if (!is_numeric($timeValue)) {
1369 $testVal = strtok($timeValue,'/-: ');
1370 if (strlen($testVal) < strlen($timeValue)) {
1372 }
1373 }
1374 $timeValue = self::_getTimeValue($timeValue);
1375 if (is_string($timeValue)) {
1377 }
1378 }
1379 // Execute function
1380 if ($timeValue >= 1) {
1381 $timeValue = fmod($timeValue,1);
1382 } elseif ($timeValue < 0.0) {
1384 }
1385 $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
1386
1387 return (int) gmdate('s',$timeValue);
1388 } // function SECONDOFMINUTE()
1389
1390
1410 public static function EDATE($dateValue = 1, $adjustmentMonths = 0) {
1412 $adjustmentMonths = PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths);
1413
1414 if (!is_numeric($adjustmentMonths)) {
1416 }
1417 $adjustmentMonths = floor($adjustmentMonths);
1418
1419 if (is_string($dateValue = self::_getDateValue($dateValue))) {
1421 }
1422
1423 // Execute function
1424 $PHPDateObject = self::_adjustDateByMonths($dateValue,$adjustmentMonths);
1425
1428 return (float) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
1432 return $PHPDateObject;
1433 }
1434 } // function EDATE()
1435
1436
1455 public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) {
1457 $adjustmentMonths = PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths);
1458
1459 if (!is_numeric($adjustmentMonths)) {
1461 }
1462 $adjustmentMonths = floor($adjustmentMonths);
1463
1464 if (is_string($dateValue = self::_getDateValue($dateValue))) {
1466 }
1467
1468 // Execute function
1469 $PHPDateObject = self::_adjustDateByMonths($dateValue,$adjustmentMonths+1);
1470 $adjustDays = (int) $PHPDateObject->format('d');
1471 $adjustDaysString = '-'.$adjustDays.' days';
1472 $PHPDateObject->modify($adjustDaysString);
1473
1476 return (float) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
1480 return $PHPDateObject;
1481 }
1482 } // function EOMONTH()
1483
1484} // class PHPExcel_Calculation_DateTime
1485
date( 'd-M-Y', $objPHPExcel->getProperties() ->getCreated())
An exception for terminatinating execution or to throw for unit testing.
static YEAR($dateValue=1)
YEAR.
Definition: DateTime.php:1253
static TIME($hour=0, $minute=0, $second=0)
Definition: DateTime.php:386
static EOMONTH($dateValue=1, $adjustmentMonths=0)
EOMONTH.
Definition: DateTime.php:1455
static DAYOFMONTH($dateValue=1)
DAYOFMONTH.
Definition: DateTime.php:1071
static _getTimeValue($timeValue)
_getTimeValue
Definition: DateTime.php:126
static HOUROFDAY($timeValue=0)
HOUROFDAY.
Definition: DateTime.php:1284
static _adjustDateByMonths($dateValue=0, $adjustmentMonths=0)
Definition: DateTime.php:135
static MONTHOFYEAR($dateValue=1)
MONTHOFYEAR.
Definition: DateTime.php:1222
static WORKDAY($startDate, $endDays)
Definition: DateTime.php:968
static DATEDIF($startDate=0, $endDate=0, $unit='D')
DATEDIF.
Definition: DateTime.php:618
static DATEVALUE($dateValue=1)
Definition: DateTime.php:481
static WEEKOFYEAR($dateValue=1, $method=1)
WEEKOFYEAR.
Definition: DateTime.php:1176
static SECONDOFMINUTE($timeValue=0)
SECONDOFMINUTE.
Definition: DateTime.php:1364
static _isLeapYear($year)
Identify if a year is a leap year or not.
Definition: DateTime.php:54
static MINUTEOFHOUR($timeValue=0)
MINUTEOFHOUR.
Definition: DateTime.php:1324
static DATE($year=0, $month=1, $day=1)
Definition: DateTime.php:292
static YEARFRAC($startDate=0, $endDate=0, $method=0)
Definition: DateTime.php:791
static TIMEVALUE($timeValue)
Definition: DateTime.php:583
static DAYS360($startDate=0, $endDate=0, $method=false)
Definition: DateTime.php:736
static DAYOFWEEK($dateValue=1, $style=1)
DAYOFWEEK.
Definition: DateTime.php:1108
static _dateDiff360($startDay, $startMonth, $startYear, $endDay, $endMonth, $endYear, $methodUS)
Return the number of days between two dates based on a 360 day calendar.
Definition: DateTime.php:71
static _getDateValue($dateValue)
_getDateValue
Definition: DateTime.php:101
static EDATE($dateValue=1, $adjustmentMonths=0)
EDATE.
Definition: DateTime.php:1410
static NETWORKDAYS($startDate, $endDate)
Definition: DateTime.php:886
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
static setReturnDateType($returnDateType)
Definition: Functions.php:155
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
const COMPATIBILITY_EXCEL
constants
Definition: Functions.php:62
static FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
FormattedPHPToExcel.
Definition: Date.php:215
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
Definition: Date.php:185
static dayStringToNumber($day)
Definition: Date.php:385
static ExcelToPHPObject($dateValue=0)
Convert a date from Excel to a PHP Date/Time object.
Definition: Date.php:160
static monthStringToNumber($month)
Definition: Date.php:374
static getExcelCalendar()
Return the Excel calendar (Windows 1900 or Mac 1904)
Definition: Date.php:106
const CALENDAR_WINDOWS_1900
constants
Definition: Date.php:40
static ExcelToPHP($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from Excel to PHP.
Definition: Date.php:120
static testStringAsNumeric($value)
Retrieve any leading numeric part of a string, or return the full string if no leading numeric (handl...
Definition: String.php:804
$style
Definition: example_012.php:70
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27