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