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