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