Subversion Repositories eFlore/Applications.cel

Rev

Rev 2388 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version		##VERSION##, ##DATE##
26
 */
27
 
28
 
29
/** PHPExcel root directory */
30
if (!defined('PHPEXCEL_ROOT')) {
31
	/**
32
	 * @ignore
33
	 */
34
	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35
	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36
}
37
 
38
 
39
/**
40
 * PHPExcel_Calculation_DateTime
41
 *
42
 * @category	PHPExcel
43
 * @package		PHPExcel_Calculation
44
 * @copyright	Copyright (c) 2006 - 2013 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