ILIAS  release_5-2 Revision v5.2.25-18-g3f80b82851
DateTime.php
Go to the documentation of this file.
1 <?php
30 if (!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') {
621  $unit = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($unit));
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);
1053  return PHPExcel_Shared_Date::ExcelToPHPObject($endDate);
1054  }
1055  } // function WORKDAY()
1056 
1057 
1071  public static function DAYOFMONTH($dateValue = 1) {
1072  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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) {
1109  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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) {
1177  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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) {
1223  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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) {
1254  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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) {
1285  $timeValue = PHPExcel_Calculation_Functions::flattenSingleValue($timeValue);
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) {
1365  $timeValue = PHPExcel_Calculation_Functions::flattenSingleValue($timeValue);
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) {
1411  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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);
1430  return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
1432  return $PHPDateObject;
1433  }
1434  } // function EDATE()
1435 
1436 
1455  public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) {
1456  $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
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);
1478  return (integer) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
1480  return $PHPDateObject;
1481  }
1482  } // function EOMONTH()
1483 
1484 } // class PHPExcel_Calculation_DateTime
1485 
$style
Definition: example_012.php:70
static DAYS360($startDate=0, $endDate=0, $method=false)
Definition: DateTime.php:736
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
static _getDateValue($dateValue)
_getDateValue
Definition: DateTime.php:101
static _isLeapYear($year)
Identify if a year is a leap year or not.
Definition: DateTime.php:54
static WEEKOFYEAR($dateValue=1, $method=1)
WEEKOFYEAR.
Definition: DateTime.php:1176
static ExcelToPHPObject($dateValue=0)
Convert a date from Excel to a PHP Date/Time object.
Definition: Date.php:160
static DATE($year=0, $month=1, $day=1)
Definition: DateTime.php:292
static DAYOFMONTH($dateValue=1)
DAYOFMONTH.
Definition: DateTime.php:1071
static TIME($hour=0, $minute=0, $second=0)
Definition: DateTime.php:386
const COMPATIBILITY_EXCEL
constants
Definition: Functions.php:62
static ExcelToPHP($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from Excel to PHP.
Definition: Date.php:120
static YEAR($dateValue=1)
YEAR.
Definition: DateTime.php:1253
Add rich text string
The name of the decorator.
static SECONDOFMINUTE($timeValue=0)
SECONDOFMINUTE.
Definition: DateTime.php:1364
static WORKDAY($startDate, $endDays)
Definition: DateTime.php:968
static DATEDIF($startDate=0, $endDate=0, $unit='D')
DATEDIF.
Definition: DateTime.php:618
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598
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
static FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
FormattedPHPToExcel.
Definition: Date.php:215
static DAYOFWEEK($dateValue=1, $style=1)
DAYOFWEEK.
Definition: DateTime.php:1108
static EDATE($dateValue=1, $adjustmentMonths=0)
EDATE.
Definition: DateTime.php:1410
static DATEVALUE($dateValue=1)
Definition: DateTime.php:481
date( 'd-M-Y', $objPHPExcel->getProperties() ->getCreated())
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 TIMEVALUE($timeValue)
Definition: DateTime.php:583
static MONTHOFYEAR($dateValue=1)
MONTHOFYEAR.
Definition: DateTime.php:1222
static EOMONTH($dateValue=1, $adjustmentMonths=0)
EOMONTH.
Definition: DateTime.php:1455
static _adjustDateByMonths($dateValue=0, $adjustmentMonths=0)
Definition: DateTime.php:135
static MINUTEOFHOUR($timeValue=0)
MINUTEOFHOUR.
Definition: DateTime.php:1324
Create styles array
The data for the language used.
static getExcelCalendar()
Return the Excel calendar (Windows 1900 or Mac 1904)
Definition: Date.php:106
static _getTimeValue($timeValue)
_getTimeValue
Definition: DateTime.php:126
static setReturnDateType($returnDateType)
Definition: Functions.php:155
static HOUROFDAY($timeValue=0)
HOUROFDAY.
Definition: DateTime.php:1284
static monthStringToNumber($month)
Definition: Date.php:374
const CALENDAR_WINDOWS_1900
constants
Definition: Date.php:40
Add data(end) time
Method that wraps PHPs time in order to allow simulations with the workflow.
static dayStringToNumber($day)
Definition: Date.php:385
static NETWORKDAYS($startDate, $endDate)
Definition: DateTime.php:886
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
Definition: Date.php:185
static YEARFRAC($startDate=0, $endDate=0, $method=0)
Definition: DateTime.php:791