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