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