|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel; |
|
4
|
|
|
|
|
5
|
|
|
use DateTime; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Exception; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
|
11
|
|
|
|
|
12
|
|
|
class Date |
|
13
|
|
|
{ |
|
14
|
|
|
use ArrayEnabled; |
|
15
|
|
|
|
|
16
|
|
|
/** |
|
17
|
|
|
* DATE. |
|
18
|
|
|
* |
|
19
|
|
|
* The DATE function returns a value that represents a particular date. |
|
20
|
|
|
* |
|
21
|
|
|
* NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date |
|
22
|
|
|
* format of your regional settings. PhpSpreadsheet does not change cell formatting in this way. |
|
23
|
|
|
* |
|
24
|
|
|
* Excel Function: |
|
25
|
|
|
* DATE(year,month,day) |
|
26
|
|
|
* |
|
27
|
|
|
* PhpSpreadsheet is a lot more forgiving than MS Excel when passing non numeric values to this function. |
|
28
|
|
|
* A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted, |
|
29
|
|
|
* as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language. |
|
30
|
|
|
* |
|
31
|
|
|
* @param array|float|int|string $year The value of the year argument can include one to four digits. |
|
32
|
|
|
* Excel interprets the year argument according to the configured |
|
33
|
|
|
* date system: 1900 or 1904. |
|
34
|
|
|
* If year is between 0 (zero) and 1899 (inclusive), Excel adds that |
|
35
|
|
|
* value to 1900 to calculate the year. For example, DATE(108,1,2) |
|
36
|
|
|
* returns January 2, 2008 (1900+108). |
|
37
|
|
|
* If year is between 1900 and 9999 (inclusive), Excel uses that |
|
38
|
|
|
* value as the year. For example, DATE(2008,1,2) returns January 2, |
|
39
|
|
|
* 2008. |
|
40
|
|
|
* If year is less than 0 or is 10000 or greater, Excel returns the |
|
41
|
|
|
* #NUM! error value. |
|
42
|
|
|
* @param array|float|int|string $month A positive or negative integer representing the month of the year |
|
43
|
|
|
* from 1 to 12 (January to December). |
|
44
|
|
|
* If month is greater than 12, month adds that number of months to |
|
45
|
|
|
* the first month in the year specified. For example, DATE(2008,14,2) |
|
46
|
|
|
* returns the serial number representing February 2, 2009. |
|
47
|
|
|
* If month is less than 1, month subtracts the magnitude of that |
|
48
|
|
|
* number of months, plus 1, from the first month in the year |
|
49
|
|
|
* specified. For example, DATE(2008,-3,2) returns the serial number |
|
50
|
|
|
* representing September 2, 2007. |
|
51
|
|
|
* @param array|float|int|string $day A positive or negative integer representing the day of the month |
|
52
|
|
|
* from 1 to 31. |
|
53
|
|
|
* If day is greater than the number of days in the month specified, |
|
54
|
|
|
* day adds that number of days to the first day in the month. For |
|
55
|
|
|
* example, DATE(2008,1,35) returns the serial number representing |
|
56
|
|
|
* February 4, 2008. |
|
57
|
|
|
* If day is less than 1, day subtracts the magnitude that number of |
|
58
|
|
|
* days, plus one, from the first day of the month specified. For |
|
59
|
|
|
* example, DATE(2008,1,-15) returns the serial number representing |
|
60
|
|
|
* December 16, 2007. |
|
61
|
|
|
* |
|
62
|
|
|
* @return array|DateTime|float|int|string Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
|
63
|
|
|
* depending on the value of the ReturnDateType flag |
|
64
|
|
|
* If an array of numbers is passed as the argument, then the returned result will also be an array |
|
65
|
|
|
* with the same dimensions |
|
66
|
|
|
*/ |
|
67
|
300 |
|
public static function fromYMD(array|float|int|string $year, null|array|bool|float|int|string $month, array|float|int|string $day): float|int|DateTime|string|array |
|
68
|
|
|
{ |
|
69
|
300 |
|
if (is_array($year) || is_array($month) || is_array($day)) { |
|
70
|
113 |
|
return self::evaluateArrayArguments([self::class, __FUNCTION__], $year, $month, $day); |
|
71
|
|
|
} |
|
72
|
|
|
|
|
73
|
299 |
|
$baseYear = SharedDateHelper::getExcelCalendar(); |
|
74
|
|
|
|
|
75
|
|
|
try { |
|
76
|
299 |
|
$year = self::getYear($year, $baseYear); |
|
77
|
293 |
|
$month = self::getMonth($month); |
|
78
|
287 |
|
$day = self::getDay($day); |
|
79
|
284 |
|
self::adjustYearMonth($year, $month, $baseYear); |
|
80
|
19 |
|
} catch (Exception $e) { |
|
81
|
19 |
|
return $e->getMessage(); |
|
82
|
|
|
} |
|
83
|
|
|
|
|
84
|
|
|
// Execute function |
|
85
|
281 |
|
$excelDateValue = SharedDateHelper::formattedPHPToExcel($year, $month, $day); |
|
86
|
|
|
|
|
87
|
281 |
|
return Helpers::returnIn3FormatsFloat($excelDateValue); |
|
88
|
|
|
} |
|
89
|
|
|
|
|
90
|
|
|
/** |
|
91
|
|
|
* Convert year from multiple formats to int. |
|
92
|
|
|
*/ |
|
93
|
299 |
|
private static function getYear(mixed $year, int $baseYear): int |
|
94
|
|
|
{ |
|
95
|
299 |
|
if ($year === null) { |
|
96
|
|
|
$year = 0; |
|
97
|
299 |
|
} elseif (is_scalar($year)) { |
|
98
|
299 |
|
$year = StringHelper::testStringAsNumeric((string) $year); |
|
99
|
|
|
} |
|
100
|
299 |
|
if (!is_numeric($year)) { |
|
101
|
3 |
|
throw new Exception(ExcelError::VALUE()); |
|
102
|
|
|
} |
|
103
|
296 |
|
$year = (int) $year; |
|
104
|
|
|
|
|
105
|
296 |
|
if ($year < ($baseYear - 1900)) { |
|
106
|
3 |
|
throw new Exception(ExcelError::NAN()); |
|
107
|
|
|
} |
|
108
|
293 |
|
if ((($baseYear - 1900) !== 0) && ($year < $baseYear) && ($year >= 1900)) { |
|
109
|
1 |
|
throw new Exception(ExcelError::NAN()); |
|
110
|
|
|
} |
|
111
|
|
|
|
|
112
|
293 |
|
if (($year < $baseYear) && ($year >= ($baseYear - 1900))) { |
|
113
|
13 |
|
$year += 1900; |
|
114
|
|
|
} |
|
115
|
|
|
|
|
116
|
293 |
|
return (int) $year; |
|
117
|
|
|
} |
|
118
|
|
|
|
|
119
|
|
|
/** |
|
120
|
|
|
* Convert month from multiple formats to int. |
|
121
|
|
|
*/ |
|
122
|
293 |
|
private static function getMonth(mixed $month): int |
|
123
|
|
|
{ |
|
124
|
293 |
|
if (is_string($month)) { |
|
125
|
15 |
|
if (!is_numeric($month)) { |
|
126
|
12 |
|
$month = SharedDateHelper::monthStringToNumber($month); |
|
127
|
|
|
} |
|
128
|
278 |
|
} elseif ($month === null) { |
|
129
|
3 |
|
$month = 0; |
|
130
|
275 |
|
} elseif (is_bool($month)) { |
|
131
|
6 |
|
$month = (int) $month; |
|
132
|
|
|
} |
|
133
|
293 |
|
if (!is_numeric($month)) { |
|
134
|
6 |
|
throw new Exception(ExcelError::VALUE()); |
|
135
|
|
|
} |
|
136
|
|
|
|
|
137
|
287 |
|
return (int) $month; |
|
138
|
|
|
} |
|
139
|
|
|
|
|
140
|
|
|
/** |
|
141
|
|
|
* Convert day from multiple formats to int. |
|
142
|
|
|
*/ |
|
143
|
287 |
|
private static function getDay(mixed $day): int |
|
144
|
|
|
{ |
|
145
|
287 |
|
if (is_string($day) && !is_numeric($day)) { |
|
146
|
18 |
|
$day = SharedDateHelper::dayStringToNumber($day); |
|
147
|
|
|
} |
|
148
|
|
|
|
|
149
|
287 |
|
if ($day === null) { |
|
150
|
|
|
$day = 0; |
|
151
|
287 |
|
} elseif (is_scalar($day)) { |
|
152
|
287 |
|
$day = StringHelper::testStringAsNumeric((string) $day); |
|
153
|
|
|
} |
|
154
|
287 |
|
if (!is_numeric($day)) { |
|
155
|
3 |
|
throw new Exception(ExcelError::VALUE()); |
|
156
|
|
|
} |
|
157
|
|
|
|
|
158
|
284 |
|
return (int) $day; |
|
159
|
|
|
} |
|
160
|
|
|
|
|
161
|
284 |
|
private static function adjustYearMonth(int &$year, int &$month, int $baseYear): void |
|
162
|
|
|
{ |
|
163
|
284 |
|
if ($month < 1) { |
|
164
|
|
|
// Handle year/month adjustment if month < 1 |
|
165
|
59 |
|
--$month; |
|
166
|
59 |
|
$year += (int) (ceil($month / 12) - 1); |
|
167
|
59 |
|
$month = 13 - abs($month % 12); |
|
168
|
230 |
|
} elseif ($month > 12) { |
|
169
|
|
|
// Handle year/month adjustment if month > 12 |
|
170
|
20 |
|
$year += intdiv($month, 12); |
|
171
|
20 |
|
$month = ($month % 12); |
|
172
|
|
|
} |
|
173
|
|
|
|
|
174
|
|
|
// Re-validate the year parameter after adjustments |
|
175
|
284 |
|
if (($year < $baseYear) || ($year >= 10000)) { |
|
176
|
3 |
|
throw new Exception(ExcelError::NAN()); |
|
177
|
|
|
} |
|
178
|
|
|
} |
|
179
|
|
|
} |
|
180
|
|
|
|
PHPOffice /
PhpSpreadsheet
