View Code - 20260721

YearFrac.php | 20260721_154033.php Initial Bulk Import

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;

use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;

class YearFrac
{
    use ArrayEnabled;

/**
     * YEARFRAC.
     *
     * Calculates the fraction of the year represented by the number of whole days between two dates
     * (the start_date and the end_date).
     * Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or
     * obligations to assign to a specific term.
     *
     * Excel Function:
     *        YEARFRAC(startDate,endDate[,method])
     * See https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html
     *     for description of algorithm used in Excel
     *
     * @param mixed $startDate Excel date serial value (float), PHP date timestamp (integer),
     *                                    PHP DateTime object, or a standard date string
     *                         Or can be an array of values
     * @param mixed $endDate Excel date serial value (float), PHP date timestamp (integer),
     *                                    PHP DateTime object, or a standard date string
     *                         Or can be an array of methods
     * @param array<mixed>|int $method Method used for the calculation
     *                                        0 or omitted    US (NASD) 30/360
     *                                        1                Actual/actual
     *                                        2                Actual/360
     *                                        3                Actual/365
     *                                        4                European 30/360
     *                         Or can be an array of methods
     *
     * @return array<mixed>|float|int|string fraction of the year, or a string containing an error
     *         If an array of values is passed for the $startDate or $endDays,arguments, then the returned result
     *            will also be an array with matching dimensions
     */
    public static function fraction(mixed $startDate, mixed $endDate, array|int|string|null $method = 0): array|string|int|float
    {
        if (is_array($startDate) || is_array($endDate) || is_array($method)) {
            return self::evaluateArrayArguments([self::class, __FUNCTION__], $startDate, $endDate, $method);
        }

try {
            $method = (int) Helpers::validateNumericNull($method);
            $sDate = Helpers::getDateValue($startDate);
            $eDate = Helpers::getDateValue($endDate);
            $sDate = self::excelBug($sDate, $startDate, $endDate, $method);
            $eDate = self::excelBug($eDate, $endDate, $startDate, $method);
            $startDate = min($sDate, $eDate);
            $endDate = max($sDate, $eDate);
        } catch (Exception $e) {
            return $e->getMessage();
        }

return match ($method) {
            0 => Helpers::floatOrInt(Days360::between($startDate, $endDate)) / 360,
            1 => self::method1($startDate, $endDate),
            2 => Helpers::floatOrInt(Difference::interval($startDate, $endDate)) / 360,
            3 => Helpers::floatOrInt(Difference::interval($startDate, $endDate)) / 365,
            4 => Helpers::floatOrInt(Days360::between($startDate, $endDate, true)) / 360,
            default => ExcelError::NAN(),
        };
    }

/**
     * Excel 1900 calendar treats date argument of null as 1900-01-00. Really.
     */
    private static function excelBug(float $sDate, mixed $startDate, mixed $endDate, int $method): float
    {
        if (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE && SharedDateHelper::getExcelCalendar() !== SharedDateHelper::CALENDAR_MAC_1904) {
            if ($endDate === null && $startDate !== null) {
                if (DateParts::month($sDate) == 12 && DateParts::day($sDate) === 31 && $method === 0) {
                    $sDate += 2;
                } else {
                    ++$sDate;
                }
            }
        }

return $sDate;
    }

private static function method1(float $startDate, float $endDate): float
    {
        $days = Helpers::floatOrInt(Difference::interval($startDate, $endDate));
        $startYear = (int) DateParts::year($startDate);
        $endYear = (int) DateParts::year($endDate);
        $years = $endYear - $startYear + 1;
        $startMonth = (int) DateParts::month($startDate);
        $startDay = (int) DateParts::day($startDate);
        $endMonth = (int) DateParts::month($endDate);
        $endDay = (int) DateParts::day($endDate);
        $startMonthDay = 100 * $startMonth + $startDay;
        $endMonthDay = 100 * $endMonth + $endDay;
        if ($years == 1) {
            $tmpCalcAnnualBasis = 365 + (int) Helpers::isLeapYear($endYear);
        } elseif ($years == 2 && $startMonthDay >= $endMonthDay) {
            if (Helpers::isLeapYear($startYear)) {
                $tmpCalcAnnualBasis = 365 + (int) ($startMonthDay <= 229);
            } elseif (Helpers::isLeapYear($endYear)) {
                $tmpCalcAnnualBasis = 365 + (int) ($endMonthDay >= 229);
            } else {
                $tmpCalcAnnualBasis = 365;
            }
        } else {
            $tmpCalcAnnualBasis = 0;
            for ($year = $startYear; $year <= $endYear; ++$year) {
                $tmpCalcAnnualBasis += 365 + (int) Helpers::isLeapYear($year);
            }
            $tmpCalcAnnualBasis /= $years;
        }

return $days / $tmpCalcAnnualBasis;
    }
}