[Summary view]

Differences Between: [Versions 400 and 401]

   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|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($startDate, $endDate, $method = 0)
  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          switch ($method) {
  65              case 0:
  66                  return Functions::scalar(Days360::between($startDate, $endDate)) / 360;
  67              case 1:
  68                  return self::method1($startDate, $endDate);
  69              case 2:
  70                  return Functions::scalar(Difference::interval($startDate, $endDate)) / 360;
  71              case 3:
  72                  return Functions::scalar(Difference::interval($startDate, $endDate)) / 365;
  73              case 4:
  74                  return Functions::scalar(Days360::between($startDate, $endDate, true)) / 360;
  75          }
  76  
  77          return ExcelError::NAN();
  78      }
  79  
  80      /**
  81       * Excel 1900 calendar treats date argument of null as 1900-01-00. Really.
  82       *
  83       * @param mixed $startDate
  84       * @param mixed $endDate
  85       */
  86      private static function excelBug(float $sDate, $startDate, $endDate, int $method): float
  87      {
  88          if (Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE && SharedDateHelper::getExcelCalendar() !== SharedDateHelper::CALENDAR_MAC_1904) {
  89              if ($endDate === null && $startDate !== null) {
  90                  if (DateParts::month($sDate) == 12 && DateParts::day($sDate) === 31 && $method === 0) {
  91                      $sDate += 2;
  92                  } else {
  93                      ++$sDate;
  94                  }
  95              }
  96          }
  97  
  98          return $sDate;
  99      }
 100  
 101      private static function method1(float $startDate, float $endDate): float
 102      {
 103          $days = Functions::scalar(Difference::interval($startDate, $endDate));
 104          $startYear = (int) DateParts::year($startDate);
 105          $endYear = (int) DateParts::year($endDate);
 106          $years = $endYear - $startYear + 1;
 107          $startMonth = (int) DateParts::month($startDate);
 108          $startDay = (int) DateParts::day($startDate);
 109          $endMonth = (int) DateParts::month($endDate);
 110          $endDay = (int) DateParts::day($endDate);
 111          $startMonthDay = 100 * $startMonth + $startDay;
 112          $endMonthDay = 100 * $endMonth + $endDay;
 113          if ($years == 1) {
 114              $tmpCalcAnnualBasis = 365 + (int) Helpers::isLeapYear($endYear);
 115          } elseif ($years == 2 && $startMonthDay >= $endMonthDay) {
 116              if (Helpers::isLeapYear($startYear)) {
 117                  $tmpCalcAnnualBasis = 365 + (int) ($startMonthDay <= 229);
 118              } elseif (Helpers::isLeapYear($endYear)) {
 119                  $tmpCalcAnnualBasis = 365 + (int) ($endMonthDay >= 229);
 120              } else {
 121                  $tmpCalcAnnualBasis = 365;
 122              }
 123          } else {
 124              $tmpCalcAnnualBasis = 0;
 125              for ($year = $startYear; $year <= $endYear; ++$year) {
 126                  $tmpCalcAnnualBasis += 365 + (int) Helpers::isLeapYear($year);
 127              }
 128              $tmpCalcAnnualBasis /= $years;
 129          }
 130  
 131          return $days / $tmpCalcAnnualBasis;
 132      }
 133  }