[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]

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