| 1441 |
ariadna |
1 |
<?php
|
|
|
2 |
|
|
|
3 |
namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
|
|
|
4 |
|
|
|
5 |
use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
|
|
|
6 |
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
|
|
|
7 |
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
|
|
|
8 |
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
|
|
|
9 |
use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;
|
|
|
10 |
|
|
|
11 |
class YearFrac
|
|
|
12 |
{
|
|
|
13 |
use ArrayEnabled;
|
|
|
14 |
|
|
|
15 |
/**
|
|
|
16 |
* YEARFRAC.
|
|
|
17 |
*
|
|
|
18 |
* Calculates the fraction of the year represented by the number of whole days between two dates
|
|
|
19 |
* (the start_date and the end_date).
|
|
|
20 |
* Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or
|
|
|
21 |
* obligations to assign to a specific term.
|
|
|
22 |
*
|
|
|
23 |
* Excel Function:
|
|
|
24 |
* YEARFRAC(startDate,endDate[,method])
|
|
|
25 |
* See https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html
|
|
|
26 |
* for description of algorithm used in Excel
|
|
|
27 |
*
|
|
|
28 |
* @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer),
|
|
|
29 |
* PHP DateTime object, or a standard date string
|
|
|
30 |
* Or can be an array of values
|
|
|
31 |
* @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer),
|
|
|
32 |
* PHP DateTime object, or a standard date string
|
|
|
33 |
* Or can be an array of methods
|
|
|
34 |
* @param array|int $method Method used for the calculation
|
|
|
35 |
* 0 or omitted US (NASD) 30/360
|
|
|
36 |
* 1 Actual/actual
|
|
|
37 |
* 2 Actual/360
|
|
|
38 |
* 3 Actual/365
|
|
|
39 |
* 4 European 30/360
|
|
|
40 |
* Or can be an array of methods
|
|
|
41 |
*
|
|
|
42 |
* @return array|float|int|string fraction of the year, or a string containing an error
|
|
|
43 |
* If an array of values is passed for the $startDate or $endDays,arguments, then the returned result
|
|
|
44 |
* will also be an array with matching dimensions
|
|
|
45 |
*/
|
|
|
46 |
public static function fraction(mixed $startDate, mixed $endDate, array|int|string|null $method = 0): array|string|int|float
|
|
|
47 |
{
|
|
|
48 |
if (is_array($startDate) || is_array($endDate) || is_array($method)) {
|
|
|
49 |
return self::evaluateArrayArguments([self::class, __FUNCTION__], $startDate, $endDate, $method);
|
|
|
50 |
}
|
|
|
51 |
|
|
|
52 |
try {
|
|
|
53 |
$method = (int) Helpers::validateNumericNull($method);
|
|
|
54 |
$sDate = Helpers::getDateValue($startDate);
|
|
|
55 |
$eDate = Helpers::getDateValue($endDate);
|
|
|
56 |
$sDate = self::excelBug($sDate, $startDate, $endDate, $method);
|
|
|
57 |
$eDate = self::excelBug($eDate, $endDate, $startDate, $method);
|
|
|
58 |
$startDate = min($sDate, $eDate);
|
|
|
59 |
$endDate = max($sDate, $eDate);
|
|
|
60 |
} catch (Exception $e) {
|
|
|
61 |
return $e->getMessage();
|
|
|
62 |
}
|
|
|
63 |
|
|
|
64 |
return match ($method) {
|
|
|
65 |
|
|
|
66 |
1 => self::method1($startDate, $endDate),
|
|
|
67 |
2 => Functions::scalar(Difference::interval($startDate, $endDate)) / 360,
|
|
|
68 |
3 => Functions::scalar(Difference::interval($startDate, $endDate)) / 365,
|
|
|
69 |
4 => Functions::scalar(Days360::between($startDate, $endDate, true)) / 360,
|
|
|
70 |
default => ExcelError::NAN(),
|
|
|
71 |
};
|
|
|
72 |
}
|
|
|
73 |
|
|
|
74 |
/**
|
|
|
75 |
* Excel 1900 calendar treats date argument of null as 1900-01-00. Really.
|
|
|
76 |
*/
|
|
|
77 |
private static function excelBug(float $sDate, mixed $startDate, mixed $endDate, int $method): float
|
|
|
78 |
{
|
|
|
79 |
if (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE && SharedDateHelper::getExcelCalendar() !== SharedDateHelper::CALENDAR_MAC_1904) {
|
|
|
80 |
if ($endDate === null && $startDate !== null) {
|
|
|
81 |
if (DateParts::month($sDate) == 12 && DateParts::day($sDate) === 31 && $method === 0) {
|
|
|
82 |
$sDate += 2;
|
|
|
83 |
} else {
|
|
|
84 |
++$sDate;
|
|
|
85 |
}
|
|
|
86 |
}
|
|
|
87 |
}
|
|
|
88 |
|
|
|
89 |
return $sDate;
|
|
|
90 |
}
|
|
|
91 |
|
|
|
92 |
private static function method1(float $startDate, float $endDate): float
|
|
|
93 |
{
|
|
|
94 |
$days = Functions::scalar(Difference::interval($startDate, $endDate));
|
|
|
95 |
$startYear = (int) DateParts::year($startDate);
|
|
|
96 |
$endYear = (int) DateParts::year($endDate);
|
|
|
97 |
$years = $endYear - $startYear + 1;
|
|
|
98 |
$startMonth = (int) DateParts::month($startDate);
|
|
|
99 |
$startDay = (int) DateParts::day($startDate);
|
|
|
100 |
$endMonth = (int) DateParts::month($endDate);
|
|
|
101 |
$endDay = (int) DateParts::day($endDate);
|
|
|
102 |
$startMonthDay = 100 * $startMonth + $startDay;
|
|
|
103 |
$endMonthDay = 100 * $endMonth + $endDay;
|
|
|
104 |
if ($years == 1) {
|
|
|
105 |
$tmpCalcAnnualBasis = 365 + (int) Helpers::isLeapYear($endYear);
|
|
|
106 |
} elseif ($years == 2 && $startMonthDay >= $endMonthDay) {
|
|
|
107 |
if (Helpers::isLeapYear($startYear)) {
|
|
|
108 |
$tmpCalcAnnualBasis = 365 + (int) ($startMonthDay <= 229);
|
|
|
109 |
} elseif (Helpers::isLeapYear($endYear)) {
|
|
|
110 |
$tmpCalcAnnualBasis = 365 + (int) ($endMonthDay >= 229);
|
|
|
111 |
} else {
|
|
|
112 |
$tmpCalcAnnualBasis = 365;
|
|
|
113 |
}
|
|
|
114 |
} else {
|
|
|
115 |
$tmpCalcAnnualBasis = 0;
|
|
|
116 |
for ($year = $startYear; $year <= $endYear; ++$year) {
|
|
|
117 |
$tmpCalcAnnualBasis += 365 + (int) Helpers::isLeapYear($year);
|
|
|
118 |
}
|
|
|
119 |
$tmpCalcAnnualBasis /= $years;
|
|
|
120 |
}
|
|
|
121 |
|
|
|
122 |
return $days / $tmpCalcAnnualBasis;
|
|
|
123 |
}
|
|
|
124 |
}
|