AddinBox(Title_Logo)


ktDATEDIF  function

There is DATEDIF function to calculate the period in Excel. However,
it is a treatment "Hidden function that exists for interchangeability
with Lotus1-2-3". Therefore, the still following bugs are not canceled as
for Excel2003 either.

  1998/10/31 to 2000/3/21    Result of MD is 19
  1998/10/31 to 2000/3/ 1    Result of MD is -1


The change was given to the DATEDIF function in Excel2007. However,
a new bug has increased without canceling the bug (When YD is specified,
Days might be calculated few for about 300 days.). The DATEDIF function
of Excel2007 is quite no use.

In the add-in software that releases it on the AddinBox site, logic according
to faithfulness is used for the rule of the periodic accounting defined by the
civil law of Japan. The macro is opened to the public and use it, please.

cf. There are bugs also in DAYS360 function and YEARFRAC function.


        ( Because I who am poor at English am translating into English while using translation software,
          there may be an odd expression. )

1.  ktDATEDIF ( UDF )
2.  This is ktDATEDIF's Bug ? No, it is not a Bug.
3.  Implementation logic of DATEDIF (MD/YD) of Excel97-2003,2010
4.  Bug of DATEDIF in Excel2007
5.  DAYS360 ( Method:False , NASD )
6.  YEARFRAC (Basis:0 , 30/360 - NASD )
7.  YEARFRAC (Basis:1 , Actual/Actual ) [ Excel ]
8.  YEARFRAC (Basis:1 , Actual/Actual ) [ OOo.Calc ( ApacheOffice , LibreOffice ) ]
9.  YEARFRAC (Basis:1 , Actual/Actual ) [ Google SpreadSheet ]



[ Link to here ]

ktDATEDIF ( UDF )
    for Excel-VBA & Calc-Basic of OpenOffice.org / ApacheOpenOffice / LibreOffice

[ 30 Oct 2008 modify ]
      [ Interval = FR ] is added.
[ 17 Jan 2012 modify ]
      I revised malfunction to become #VALUE by overflow when the distance of two dates is over 2730 years.
          (Before)  intMonth As Integer      (After)  lngMonth As Long


 ' ktDATEDIF calculates the number of years, months and days according to
 ' Day-Counting method defined by a law of Japan.


Public Function ktDATEDIF(ByVal StartDate As Date, _
                          ByVal EndDate As Date, _
                          ByVal Interval As StringAs Variant
'
' [ http://addinbox.sakura.ne.jp/Excel_Tips05_E.htm#ktDATEDIF ]
'
' [Interval]
'    YMD:"y Years m Months d Days" String format.
'    Y : The number of years in the period.
'    M : The number of months in the period.
'    YM: The number of months that do not reach one year.
'    MD: The number of days that do not reach one month.
'    YD: The number of days that do not reach one year.
'    FR: The number of years including fraction between two dates. Double format.

'(Note)
' The calculation method of "Interval=FR" is different from YEARFRAC(Basis:1,
' Act/Act) of Excel and OOo.Calc. It is a Japanese specification.
' It is calculated for each full year, counting forwards from Start-date,
' and the remaining final stub period is divided by 365 or 366.
' When the remaining final stub period will be extended in one year,
' and February 29 is included there, the denominator is 366 days. Another
' is 365 days.
' (ex.)
'  2003/5/10 - 2007/6/20 --> 4 years 41 days --> 4.1120218579 ( =41/366)
'    [2003/5/10(ex) - 2007/5/10(in)] is 4 years.
'    And [2007/5/11(in) - 6/20(in)] is 41 days.
'    One year is 366 days because there is February 29
'    in [2007/5/11(in) - 6/20 - 2008/5/10(in)].
'
'  2003/5/10 - 2008/6/20 --> 5 years 41 days --> 5.1123287671 ( =41/365)
'    [2003/5/10(ex) - 2008/5/10(in)] is 5 years.
'    And [2008/5/11(in) - 6/20(in)] is 41 days.
'    One year is 365 days because there is no February 29
'    in [2008/5/11(in) - 6/20 - 2009/5/10(in)].

Dim wkDate As Date
Dim lngMonth As Long             'Total months(example; 13 months)
Dim intYear As Integer
Dim intMonth_in_Year As Integer
Dim intDay_in_Month As Integer
Dim intDay_in_Year As Integer
Dim dblFrac As Double
Dim intFracDays As Integer

  If (StartDate > EndDate) Then
    ktDATEDIF = CVErr(xlErrValue)
    'ktDATEDIF = "Error"            'CVErr(xlErrValue) is not usable in OOo.Basic
    Exit Function
  End If

'Note: The DateDiff function of VBA calculate without including a start day
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  If (Day(StartDate + 1) = 1) Then
    ' Regardless of a large month / an even month / a leap year,
    ' it is just N month for the period until a day on the end of
    ' the month from a day on the end of the month.
    ' Shift "the end of the month day - EndDay" on 1st not to be
    ' influenced in a large month / an even month / a leap year;
    ' and calculate it by "1st - (EndDay+1)".
    lngMonth = DateDiff("m", (StartDate + 1), (EndDate + 1))
    intYear = lngMonth \ 12
    intMonth_in_Year = lngMonth Mod 12
    If (Day(EndDate + 1) = 1) Then   'Is EndDate a day on the end of the month?
      intDay_in_Month = 0
    Else
      intDay_in_Month = Day(EndDate)
    End If

    'The day of [StartDate+1] is 1st.
    wkDate = DateAdd("yyyy", intYear, (StartDate + 1))
    intDay_in_Year = DateDiff("d", wkDate, (EndDate + 1))

    If (intDay_in_Year > 0) Then
        intFracDays = DateAdd("yyyy", 1, wkDate) - wkDate      '365 or 366
    End If
  Else
    'It is just N months from StartDate to the same "Day" as StartDate,
    'when StartDate is not a day of the end of the month.
    lngMonth = DateDiff("m", StartDate, EndDate)
    wkDate = DateAdd("m", lngMonth, StartDate)
    If (wkDate > EndDate) Then
      lngMonth = lngMonth - 1
      wkDate = DateAdd("m", lngMonth, StartDate)
    End If
    intDay_in_Month = DateDiff("d", wkDate, EndDate)
    intYear = lngMonth \ 12
    intMonth_in_Year = lngMonth Mod 12

    wkDate = DateAdd("yyyy", intYear, StartDate)
    intDay_in_Year = DateDiff("d", wkDate, EndDate)

    If (intDay_in_Year > 0) Then
        intFracDays = DateAdd("yyyy", 1, wkDate) - wkDate      '365 or 366
    End If
  End If

' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  Select Case UCase(Interval)
    Case "YMD"
      ktDATEDIF = intYear & " Years " & _
                    intMonth_in_Year & " Months " & intDay_in_Month & " Days"
    Case "Y"
      ktDATEDIF = intYear
    Case "M"
      ktDATEDIF = lngMonth
    Case "YM"
      ktDATEDIF = intMonth_in_Year
    Case "MD"
      ktDATEDIF = intDay_in_Month
    Case "YD"
      ktDATEDIF = intDay_in_Year
    Case "FR"
      If (intDay_in_Year > 0) Then
        dblFrac = intDay_in_Year / CDbl(intFracDays)  'CDbl for Real calculation
        ktDATEDIF = CDbl(intYear + dblFrac)
      Else
        'There is no decimal because no days of less than one year.
        ktDATEDIF = CDbl(intYear)
      End If
    Case Else
      ktDATEDIF = CVErr(xlErrValue)
      'ktDATEDIF = "Error"            'CVErr(xlErrValue) is not usable in OOo.Basic
  End Select
End Function




[ Link to here ]

  cf.  Translation Project of Japanese Statutes

  Civil Code of Japan
      Part I General Provisions
          Chapter VI Calculation of Period


  Article 139 (Commencement of Period)
      When a period is defined by the hour, the period commences immediately at the specified time.

  Article 140
      When a period is defined by the day, week, month, or year, the first day of the period shall not
      be included for the purpose of the calculation; provided, however, that, this shall not apply to
      the cases where the period commences at twelve midnight.

  Article 141 (Expiration of Period)
      In the case referred to in the preceding Article, the period shall expire at the end of the last day
      of such period.

  Article 143 (Calculation of Period with Reference to Calendar)
      1. When a period is defined by the week, month, or year, the period shall be calculated with
          reference to the calendar week, month, or year.
      2. When a period does not commence at the beginning of the week, month, or year, such period
          shall expire in the last week, month, or year on the day immediately preceding the day which
          corresponds to the commencement day; provided, however, that if the period is defined by the
          month or year and the last month does not contain the corresponding day, the period shall
          expire on the last day of such month.

  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  These rules can be rewritten in a plain sentence as follows.

  a ) The first day of the period is not counted. That is, the date specified for StartDate of the
      argument is not counted, and will be counted from the next day.

  b ) When counting from "1st" (That is, the day of the end of the month is specified for StartDate
      of the argument), regardless of a large month / an even month / a leap year, it is just N months
      on a day of the end of the month and it is just N years on the day of the end of the month of the
      month as the same month of StartDate.

  c ) When counting from excepted "1st" (That is, the day of the end of the month is not specified
      for StartDate of the argument), it is just N months on the same day as StartDate, it is just N
      years on the same month and the same day as StartDate.
      However, regard of a large month / an even month / a leap year, When "Same day or same day of
      the same month" corresponding to StartDate doesn't exist, it is "just N months" and "just N years"
      from StartDate to the day of the end of the month.


  Note:
      The quotient that divides the number of months of "just N months (13 months etc.)" by 12 is
      a result of [Y], and the remainder is a result of [YM].

      The result of [MD] will be days from the next day of "the date that becomes just N months" to
      EndDate (The next day is included. ).

      The result of [YD] will be days from the next day of "the date that becomes just N years" to
      EndDate (The next day is included. ).

      Attention : The DATEDIF function might return a not corresponding result to the calculation
                            of the above-mentioned specification ( It is a bug of the DATEDIF function ).



[ Link to here ]

This is ktDATEDIF's Bug ? No, it is not a Bug.

There was a question to ktDATEDIF by an email.

 > 28-Oct-1998 to 17-Mar-2000 , 1y 4m 18d
 > 29-Oct-1998 to 17-Mar-2000 , 1y 4m 17d
 > 30-Oct-1998 to 17-Mar-2000 , 1y 4m 17d
 > 31-Oct-1998 to 17-Mar-2000 , 1y 4m 17d
 >
There is a bug in the ktDATEDIF UDF.
 
 "Though StartDate advances, the days do not decrease."
 Did you regard this as a bug?
 
 No, it is not a bug.
 This counting is based on the definition of the law of Japan.
 
 Please refer to the follows.
 http://addinbox.sakura.ne.jp/Excel_Tips05_E.htm#civil
 And, refer to the commentary after follows.
 > These rules can be rewritten in a plain sentence as follows.
 
 
                                  DATEDIF  , ktDATEDIF
 a) 28-Oct-1998 to 17-Mar-2000 , 1y 4m 18d , 1y 4m 18d
 b) 29-Oct-1998 to 17-Mar-2000 , 1y 4m 17d , 1y 4m 17d
 c) 30-Oct-1998 to 17-Mar-2000 ,[1y 4m 16d , 1y 4m 17d]
 d) 31-Oct-1998 to 17-Mar-2000 ,[1y 4m 15d , 1y 4m 17d]
 e) 01-Nov-1998 to 17-Mar-2000 , 1y 4m 16d , 1y 4m 16d

 cf. in DATEDIF
 As StartDate advances (28-Oct,29-Oct,30-Oct,31-Oct), the days decrease.
 However, the days increase adversely as they advance from 31-Oct to 1-Nov.
 
 
 Day-Counting in Japan such as "just N months and D days (remaining final
 stub period)" count towards EndDate from StartDate.
 And, the initial date of reckoning is the next date of StartDate.
 
 a) 28-Oct-1998(exclude) 29-Oct(include) to 17-Mar-2000
 b) 29-Oct-1998(exclude) 30-Oct(include) to 17-Mar-2000
 c) 30-Oct-1998(exclude) 31-Oct(include) to 17-Mar-2000
 d) 31-Oct-1998(exclude) 01-Nov(include) to 17-Mar-2000
 e) 01-Nov-1998(exclude) 02-Nov(include) to 17-Mar-2000
 
 Case (a)
   [29-Oct to 28-Feb] is "just 16 months (1 years 4 months).
   And, Next Date(29-Feb-2000,include) to 17-Mar is 18 days.
 
 Case (b)
   [30-Oct to 29-Feb-2000] is "just 16 months (1 years 4 months).
   And, Next Date(1-Mar,include) to 17-Mar is 17 days.
 
 Case (c)
   [31-Oct to 30-Feb] is "just 16 months (1 years 4 months).
   However, there is not 30th in February. In this case,
   I count "just 16 months" on the end of the month (29-Feb-2000).
   And, Next Date(1-Mar,include) to 17-Mar is 17 days.
 
 Case (d)
   In the case of the counting from "the beginning day of the month",
   it count with "just N months" in the end of the month regardless of
   "large-month , small-month and leap year".
   Therefore, [1-Nov to end-of-month (29-Feb-2000)] is "just 16 months
   (1 years 4 months).
   And, Next Date(1-Mar,include) to 17-Mar is 17 days.
 
 Case (e)
   [2-Nov to 1-Mar] is "just 16 months (1 years 4 months).
   And, Next Date(2-Mar,include) to 17-Mar is 16 days.
 
 
 - Reference (common year) -
                                DATEDIF , ktDATEDIF
 27-Oct-1999 to 17-Mar-2001 , 1y 4m 18d , 1y 4m 18d
 28-Oct-1999 to 17-Mar-2001 , 1y 4m 17d , 1y 4m 17d
 29-Oct-1999 to 17-Mar-2001 , 1y 4m 16d , 1y 4m 17d
 30-Oct-1999 to 17-Mar-2001 , 1y 4m 15d , 1y 4m 17d
 31-Oct-1999 to 17-Mar-2001 , 1y 4m 14d , 1y 4m 17d
 01-Nov-1999 to 17-Mar-2001 , 1y 4m 16d , 1y 4m 16d
 
 
 ktDATEDIF is based on the definition of the law of Japan.
 I'm sorry, I do not know the method of the Day-Counting that are
 right (generally performed) in your country (or your business).
 When the method does not accord, you should not use ktDATEDIF.
 



[ Link to here ]

Implementation logic of DATEDIF (MD/YD)
                              of Excel97-2003 , 2010 , 2013


It is an expectation logic performed inside of DATEDIF which Tsunoda & Housaka
analyze a result (START & END VS Y-M-D) of DATEDIF, and arrived.

In addition, DATEDIF of Excel2007 is different from this logic because there is
the following bug.


--- Implementation logic of DATEDIF (MD)
                                      in Excel97-2003 , 2010 , 2013 ---


 Function DateDif_MD(Date1 As Date, Date2 As Date) As Long
 If (Day(Date2) >= Day(Date1)) Then
     DateDif_MD = Day(Date2) - Day(Date1)
 Else
     DateDif_MD = Date2 - DateSerial(Year(Date2), Month(Date2) - 1, Day(Date1))
 End If
 End Function
 

--- Implementation logic of DATEDIF (YD)
                                      in Excel97-2003 , 2010 , 2013 ---


 Function DateDif_YD(Date1 As Date, Date2 As Date) As Long
 Dim d1 As Date
 Dim d2 As Date
 
 'Shift StartDate and EndDate in the past on [day of StartDate].
 'By this shift, the period is simplified during [the period from 1st of month].

 d1 = Date1 - Day(Date1) + 1
 d2 = Date2 - Day(Date1) + 1
 
 If (Month(d1) <= Month(d2)) Then
     DateDif_YD = DateSerial(Year(d1), Month(d2), Day(d2)) - d1
 Else
     DateDif_YD = DateSerial(Year(d1) + 1, Month(d2), Day(d2)) - d1
 End If
 End Function
 


[ Link to here ] 

Bug of DATEDIF in Excel2007

 ktDATEDIF  vs  DATEDIF(Excel97 - 2003)  vs  DATEDIF(Excel2007)

      ( 25-Mar-2008 )
            Microsoft formally admits that trouble is found in the DATEDIF function of Excel2007.
            Microsoft did not answer even the presence of the bug for the reasons why DATEDIF was
            a hidden function so far.

                                                     ( Years - Months - Days )
( Y , YM , MD )            ktDATEDIF      Excel2003       Excel2007(before SP1)
2008/ 3/31 - 2009/ 2/28    0y 11m  0d      0y 10m 28d      0y 10m 25d
2008/ 4/30 - 2009/10/31    1y  6m  0d      1y  6m  1d      1y  6m  1d
2008/ 4/30 - 2009/ 3/ 1    0y 10m  1d      0y 10m -1d      0y 10m  2d
2008/ 5/31 - 2009/ 3/ 1    0y  9m  1d      0y  9m -2d      0y  9m  1d
2008/ 2/29 - 2009/ 2/28    1y  0m  0d      0y 11m 30d      0y 11m 27d
2008/ 2/ 6 - 2012/ 3/ 3    4y  0m 26d      4y  0m 26d      4y  0m 28d
2006/12/31 - 2007/ 2/ 1    0y  1m  1d      0y  1m  1d      0y  1m -2d

( YD )                 ktDATEDIF   Excel2003   Excel2007(before SP1)
2001/ 1/1 - 2004/ 2/28     58      58          58      (Not Leap to Leap)
2001/ 1/1 - 2004/ 2/29     59     [59]        [59]      59 repeats
2001/ 1/1 - 2004/ 3/ 1     60     [59]        [59]
2001/ 1/1 - 2004/ 3/ 2     61      60          60

2000/ 1/1 - 2005/ 2/28     58      58          58      (Leap to Not Leap)
2000/ 1/1 - 2005/ 3/ 1     59      60          60       59 falls out
2000/ 1/1 - 2005/ 3/ 2     60      61          61

2001/ 3/1 - 2004/ 1/31    336     336         336    A strange calculation
2001/ 3/1 - 2004/ 2/ 1    337     337        [305]             of Excel2007
2001/12/1 - 2003/11/ 1    335     335        [ 21]

2001/ 5/1 - 2003/ 1/ 1    245     245         245  
2001/ 5/1 - 2003/ 1/31    275     275         275 
2001/ 5/1 - 2003/ 2/ 1    276     276        [244]
2001/ 5/1 - 2003/ 2/28    303     303        [271]
2001/ 5/1 - 2003/ 3/ 1    304     304        [243]
2001/ 5/1 - 2003/ 3/31    334     334        [273]
2001/ 5/1 - 2003/ 4/ 1    335     335        [242]
2001/ 5/1 - 2003/ 4/30    364     364        [271]
2001/ 5/1 - 2003/ 5/ 1      0       0           0  
2001/ 5/1 - 2003/ 5/31     30      30          30  
2001/ 5/1 - 2003/ 6/ 1     31      31          31  
2001/ 5/1 - 2003/ 6/30     60      60          60  
2001/ 5/1 - 2003/ 7/ 1     61      61          61  
2001/ 5/1 - 2003/ 7/31     91      91          91  
2001/ 5/1 - 2003/12/ 1    214     214         214  
2001/ 5/1 - 2003/12/31    244     244         244  


     ( 15-Jul-2009 )
          It was corrected by Office2007 SP2 and the update given later.
            (a) 24-Apr-2009 , SP2 [ Excel2007 12.0.6425.1000 ]
            (b) 30-Apr-2009 , KB968863 & KB969952(for later than SP1) [ Excel2007 12.0.6504.5001 ]
            (c)  9-Jun-2009 , KB969682 (Security updates for SP2. KB968863 & KB969952 is included.)
          The calculation result of MD returned before Excel2003.
          The calculation result of YD returned before Excel2003, but a new calculation error is given to
          the part (In case of " the Day of StartDate is later 2nd ", and " the Month of EndDate is January "
          , and  " the Year of EndDate is the leap year " ).
          Results are different by an update enforcement state.
            (a) before Excel2003  :  5-May-2000  to  1-Jan-2004  is  YD= 241.
            (b) [12.0.6425.1000]  :  5-May-2000  to  1-Jan-2004  is  YD= 408.
            (c) [12.0.6504.5001]  :  5-May-2000  to  1-Jan-2004  is  YD= 354 [ Result = Right + 113 ] .
                [ Bug condition ]
                    EndDate: YEAR is the leap year. MONTH is January. DAY is less than the day of StartDate.

     ( 26-Feb-2010 )
          The calculation result by Excel2010(Beta) is the same as Excel2007(SP2).

     ( 28-May-2010 )
          It was corrected by Excel2010 ( RTM ).
          The calculation result by Excel2010(RTM) is the same before Excel2003.
          (The bug that there was before Excel2003 is left.)

     ( 9-Apr-2011 )
          I reported [15-Jul-2009] that "MD was revised" by the correction of Excel2007SP2,
          but there is "the correction leak of MD".
                Ex.   20-Mar-2011 to 19-Jan-2012
                        Result = 143 days (the right answer: 30 days   [ Result = Right + 113 ] )

                [ Bug condition ]
                    EndDate: YEAR is the leap year. MONTH is January. DAY is less than the day of StartDate.

          In addition, in Excel2010, this malfunction is revised.

     ( 10-Nov-2011 )
          By the correction of Excel2007SP3, "the correction leak of MD" mentioned above is not revised either.


     [ Link to here ] 
     ( 20-Feb-2012 )
          Knowledge Base ( KB2678753 ) about this malfunction was released.
          (It is only a Japanese edition. The English edition is not yet released.)
                http://support.microsoft.com/kb/2678753/ja
                ( It is corrected in response to malfunction of following 14-Feb-2014. )


            -- Symptoms --
            When unit is YD or MD, and End_date accords in the following condition in Excel2007(SP3) ,
            the result of DATEDIF becomes 113 many values than a right value.
                  Leap year (2000, 2004, 2008, 2012, etc)
                  and  January
                  and  [ Day (Start_date) > Day (End_date) ]
            Notes : A condition and a symptom of the malfunction are different from the above
                      in the SP3 non-application.

            -- Workaround -- ( Requirement: Apply Service Pack 3 to Excel2007. )
       [YD] =IF(AND(INFO("release")="12.0",
                     MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1))
                ,DATEDIF(A1,B1,"YD")-113, DATEDIF(A1,B1,"YD"))

       [MD] =IF(AND(INFO("release")="12.0",
                     MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1))
                ,DATEDIF(A1,B1,"MD")-113, DATEDIF(A1,B1,"MD"))

            A bug does not completely disappear.
            A bug of DATEDIF in before Excel2003 and Excel2010 is left.




     [ Link to here ] 
     ( 14-Feb-2014 )
            In the following condition, malfunction to become [ Result = Right + 113 ] remained in MD and YD.
            It was recognized that [ + 113 ] was changed to [ + 164 ] by KB2827324 (8-Oct-2013 release) .
            ( There is no change in the condition. )
                [ Bug condition ]
                    EndDate: YEAR is the leap year. MONTH is January. DAY is less than the day of StartDate.

     ( 7-Mar-2014 )
            For the malfunction mentioned above, a commentary of MS-KB(KB2678753 , 20-Feb-2012 release)
            was revised from the following workaround.



     [ Link to here ] 
            This strange error value has the following careers by a past correction.
                (a) SP2 (12.0.6425.1000) ... [+164]
                (b) SP2 + KB969682 (12.0.6504.5001) ... [+113]
                (c) SP3 (12.0.6611.1000 (SP2 + KB969682)) ... [+113]
                (d) SP3 + KB2760583  (12.0.6679.5000) ... [+113]   ( 10-Sep-2013 release )
                (e) SP3 + KB2827324 (12.0.6683.5002) ... [+164]   ( 8-Oct-2013 release )

            This error value may change by a future correction again. Therefore, it is incomplete by the
            solution to change the revision expression mentioned above from 113 to 164 simply.

            It is not best and an expression becomes longer, but can cope without depending on the update
            situation by the following methods.


            -- Workaround --
            (1) Find an error value to appear in Excel2007 on the PC.
            (2) Apply the error value to [113] position in the revision expression.

            The calculation result of the period of [ 2-Jan-2011 to 1-Jan-2012 ] is follows.
                    [YD] Right answer = 364  ,  [MD] Right answer = 30

            Therefore, you can get a revision value such as 113 or 164 in the following expression.
                    [YD] DATEDIF(DATE(2011,1,2), DATE(2012,1,1), "YD") - 364
                    [MD] DATEDIF(DATE(2011,1,2), DATE(2012,1,1), "MD") - 30

      [ YD ]
      =IF(AND(INFO("release")="12.0",
              MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1)),
        DATEDIF(A1,B1,"YD")-(DATEDIF(DATE(2011,1,2),DATE(2012,1,1),"YD")-364),
        DATEDIF(A1,B1,"YD"))

      [ MD ]
      =IF(AND(INFO("release")="12.0",
              MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1)),
        DATEDIF(A1,B1,"MD")-(DATEDIF(DATE(2011,1,2),DATE(2012,1,1),"MD")-30),
        DATEDIF(A1,B1,"MD"))




            In addition, there is the risk that the person who does not know the reason of the expression
            changes the fixation date (2-Jan-2011 , 1-Jan-2012).

            Therefore, please register the part of the expression of the revision value with [ Define Name ]
            to avoid this risk.

      -- Contents of Define Name --
      [ Formulas tab / Define Name ]
      Name : Excel2007YDrevision
        Scope : Workbook
        Comment : Revise the error by the bug of Excel2007/DATEDIF(YD)
        Refers to : =DATEDIF(DATE(2011,1,2), DATE(2012,1,1), "YD") - 364
      Name : Excel2007MDrevision
        Scope : Workbook
        Comment : Revise the error by the bug of Excel2007/DATEDIF(MD)
        Refers to : =DATEDIF(DATE(2011,1,2), DATE(2012,1,1), "MD") - 30




            When use Define Name; come to follows-expression.
      [ YD ]
      =IF(AND(INFO("release")="12.0",
              MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1)),
          DATEDIF(A1,B1,"YD") - Excel2007YDrevision,
          DATEDIF(A1,B1,"YD"))

      [ MD ]
      =IF(AND(INFO("release")="12.0",
              MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1)),
          DATEDIF(A1,B1,"MD") - Excel2007MDrevision,
          DATEDIF(A1,B1,"MD"))





[ Link to here ]

DAYS360 ( Method:False , NASD )

DAYS360(NASD) has the bug (example 28-Feb-2006 to 28-Feb-2006 , result is -2). The
calculation of days of NASD is calculated according to the rule of SIA. However, DAYS360
(NASD) is not equal to the rule of SIA. DAYS360(NASD) doesn't execute the processing of
(1) of the SIA rule.

( Microsoft admits that this is a bug of Excel (24-Jun-2006). )


      (30/360 SIA)
        1. If the last date of the accrual period is the last day of February and
           the first date of the period is the last day of February, then the last
           date of the period will be changed to the 30th.

        2. If the first date of the accrual period falls on the 31st of a month or is
           the last day of February, that date will be changed to the 30th of the
           month.

        3. If the first date of the accrual period falls on the 30th of a month after
           applying (2) above, and the last date of the period falls on the 31st of a
           month, the last date will be changed to the 30th of the month.


There is a method defined only by (2) (3) of the above-mentioned rule, and it is 30/360(PSA).
When the same end of February as StartDate and EndDate is specified, the result becomes
-2 in PSA. The difference between the SIA rule and the PSA rule is understood well when
comparing it by days from the end of February to the end of February that becomes just 1
year (29-Feb-2004 to 28-Feb-2005 etc.) .

        SIA  : 360 (Regardless of leap year and not leap year)
        PSA : 358 (Not leap year to Not leap year , Leap year to not leap year)
                   359 (Not leap year to leap year)



Calculation that conforms to rule of SIA by using DAYS360(NASD) in Excel.

 =IF(AND(TEXT(StartDate + 1, "MMDD")="0301" , TEXT(EndDate + 1, "MMDD")="0301") ,
      360*(YEAR(EndDate) - YEAR(StartDate)) , DAYS360(StartDate, EndDate, FALSE))



( Note )
    DAYS360(NASD) of OOo.Calc(ApacheOffice, LibreOffice) and GoogleSpreadSheet
    is not SIA conformity as well as Excel. It is PSA.


[ Link to here ]

YEARFRAC (Basis:0 , 30/360 - NASD )

When the same end of February is specified for StartDate and EndDate, the calculation of
YEARFRAC(NASD) seems to be calculated according to the rule of SIA (Excel97-2007).

                                                          [Left: DAYS360 (StartDate, EndDate, FALSE) / 360 ]
                                                          [Right: YEARFRAC (StartDate, EndDate, 0) ]
    29-Feb-2004 to 29-Feb-2008      3.9972      4.0000
    28-Feb-2005 to 29-Feb-2008      2.9972      3.0000
    28-Feb-2005 to 28-Feb-2009      3.9944      4.0000


However, the calculation result of YEARFRAC(NASD) is different in Excel97 and
Excel2000-2007. The difference appears by the combination "31th of the great month
from the end of February".

                                                                        [Left: Excel97 ]    [Right: Excel2000-2007 ]
    28-Feb-2002 to 30-Dec-2002    0.83333     0.83333
    28-Feb-2002 to 31-Dec-2002    0.83333    [0.83611]
    29-Feb-2004 to 30-Dec-2004    0.83333     0.83333
    29-Feb-2004 to 31-Dec-2004    0.83333    [0.83611]
    29-Feb-2004 to 30-Jan-2005    0.91667     0.91667
    29-Feb-2004 to 31-Jan-2005    0.91667    [0.91944]

With Excel2000, The condition of "End of February" seemed to disappear from the definition
of the bold-face and under-line part of following SIA rule (The beginning day is meant at 30th
or 31st or the end of February. ) and to have become 'The beginning day was the 30th or 31'.

      (30/360 SIA)
        2. If the first date of the accrual period falls on the 31st of a month or is
           the last day of February, that date will be changed to the 30th of the
           month.

        3. If the first date of the accrual period falls on the 30th of a month
           after applying (2) above, and the last date of the period falls on the 31st
           of a month, the last date will be changed to the 30th of the month.


As a result, when StartDate is the end of February, the rule of (3) is not applied. That is, the 31st
of EndDate is added to the count. Therefore, days increase one day, and it becomes different the 
above-mentioned.
( Microsoft admits that this is a bug of Excel2000-2007 (24-Jun-2006).  )

    (300÷360=0.83333 , 301÷360=0.83611 , 330÷360=0.91667 , 331÷360=0.91944)



( Note )
    YEARFRAC(NASD) of OOo.Calc(ApacheOffice, LibreOffice) and GoogleSpreadSheet
    is PSA unlike Excel (SIA or SIA with the bug).




The calculation by the NASD method is used as for various financial functions besides
DAYS360 / YEARFRAC. However, it doesn't verify whether the calculation of the NASD
method is correct or not correct about them.



[ Link to here ]

YEARFRAC ( Basis:1,Act/Act )   [ Excel ]

For a calculation method of the Year Fraction by the actual days, some methods
including ISDA, ISMA, AFB are defined. However, YEARFRAC(Basis:1,Act/Act)
of Excel is equal with these none. As for the answer that referred to Microsoft,
"the reason that why adopted this calculation method did not understand even
Microsoft in itself now".  (Oct. 22 2008)

According to the analysis of the real calculation result, Excel adopts the following
calculation procedures.

(1) In the case of "the Start-date and the End-date are the same year".
        Divide the days of the period by annual days of the year(365 or 366).

(2) The period extends to plural years.

    (a) In the case of the period (except the Start-date) is less than one year.
            If there is February 29 during a period (include the Start-date); the annual
            days are 366. If there is not it; the annual days are 365.
            Then, the days during a period are divided by the annual days.

    (b) In the case of the period (except the Start-date) is over one year.
            Calculate the average of the annual days (365 or 366) of all years that a period
            extends to. Then, the days during a period are divided by the average value
            (365.25 , 365.33 etc.). In addition, even in the case of [the Start-date is December 31],
            the year of the Start-date is included in a calculation of the average.

Judging from a rule of the noninclusion for the first day, the underline part is a mistake obviously.


--- Implementation logic (Excel) ---

 Public Function YEARFRAC_Act _
           (ByVal StartDate As Date, ByVal EndDate As Date) As Double
 Dim wkDate As Date
 Dim dtmLeapDay As Date
 Dim dblDaysYear As Double
 Dim lngDays As Long
 
 wkDate = DateAdd("yyyy", 1, StartDate)
 
 'Start-date and End-date is the same year.
 If (Year(StartDate) = Year(EndDate)) Then
     If (Month(DateSerial(Year(StartDate), 2, 29)) = 2) Then
         dblDaysYear = 366
     Else
         dblDaysYear = 365
     End If
 
 'Less than one year.
 ElseIf (EndDate <= wkDate) Then
     If (Month(DateSerial(Year(StartDate), 2, 29)) = 2) Then
         'Leap year(within the year) or Leap-year to Common-year
         dtmLeapDay = DateSerial(Year(StartDate), 2, 29)
         If (StartDate <= dtmLeapDay) And (dtmLeapDay <= EndDate) Then
             dblDaysYear = 366
         Else
             dblDaysYear = 365
         End If
     ElseIf (Month(DateSerial(Year(EndDate), 2, 29)) = 2) Then
         'Common-year to Leap-year
         dtmLeapDay = DateSerial(Year(EndDate), 2, 29)
         If (StartDate <= dtmLeapDay) And (dtmLeapDay <= EndDate) Then
             dblDaysYear = 366
         Else
             dblDaysYear = 365
         End If
     Else
         'Common-year to Common-year
         dblDaysYear = 365
     End If
 
 'Over one year.
 Else
     lngDays = DateSerial(Year(EndDate), 12, 31) _
                   - DateSerial(Year(StartDate), 1, 0)
     dblDaysYear = lngDays / (Year(EndDate) - Year(StartDate) + 1)
 End If
 
 YEARFRAC_Act = (EndDate - StartDate) / dblDaysYear
 End Function
 


[ Link to here ]

YEARFRAC ( Basis:1,Act/Act )  [ OOo.Calc ( ApacheOffice , LibreOffice ) ]

With the OASIS/ODF specifications, YEARFRAC(Act/Act) is defined as AFB conformity,
but the calculation method of OOo.Calc does not accord with AFB.

According to the analysis of the real calculation result, OOo.Calc adopts the following
calculation procedures.

    a) Calculate the period of the form [Y years and remainning D days] from the
        Start-date and the End-date. (The remainning final stub period )
    b) Fraction = Y + (D / annual days [365 or 366])
    c) The annual days are the days of [year of the Start-date].
        Even in the case of [the Start-date is December 31], it is the annual days of
        [year of the Start-date].

But, for the procedure mentioned above, there is the gap of +-1 days in connection with
the end of February. When I analyze it, In the chart below, the calculation of the days
during the period of the doublet part (remainning D days) is the following procedure.


 ---------(Just Y years)------><--(remainning D days)-->
 -#-------------------#--------*==========#============*------#-- (#:Jan. 1)
                      <--------[a]-------><-----[c]---->
                      <--[b]-->
 

(i)    D = [c] + ( [a]) - [b] )        [a] [b] [c] is a days in year from January 1 (1-366).
(ii)   The value(365 or 366) of [a] is the annual days of the year of the Start-date,
       not the annual days of the year of [a] ([a] is the last year of the End-date).
(iii) [b] is the last year of the End-date, but calculates it in the year of the End-date
      for some reason.
It is made addition and subtraction even more [1 day] of February 29 by a mistake of
(ii) and (iii).

--- Implementation logic (OOo.Calc) ---

 Public Function OOo_YEARFRAC_
         (ByVal StartDate As Date, ByVal EndDate As Date) As Double
 Dim intYears As Integer
 Dim intDays As Integer
 Dim SDate As Integer
 Dim EDate As Integer
 
 SDate = DatePart("y", _
               DateSerial(Year(EndDate), Month(StartDate), Day(StartDate)))
 EDate = DatePart("y", EndDate)
 
 If (Format(StartDate, "mmdd") <= Format(EndDate, "mmdd")) Then
     intYears = Year(EndDate) - Year(StartDate)
     intDays = EDate - SDate
 Else
     If ((Year(StartDate) + 1) = Year(EndDate)) Then
         intYears = 0
         intDays = EndDate - StartDate
     Else
         intYears = Year(EndDate) - Year(StartDate) - 1
         If (Month(DateSerial(Year(StartDate), 2, 29)) = 2) Then
             intDays = (366 - SDate) + EDate
         Else
             intDays = (365 - SDate) + EDate
         End If
     End If
 End If
 
 If (Month(DateSerial(Year(StartDate), 2, 29)) = 2) Then
     OOo_YEARFRAC = intYears + CDbl(intDays) / 366#
 Else
     OOo_YEARFRAC = intYears + CDbl(intDays) / 365#
 End If
 End Function
 

Because OOo is an open software, a source code can look.
It is follows to have edited a source code (GetYearFrac & GetDiffParam) of OOo.Calc
in a VBA style.

--- Source code of OOo.Calc (edit VBA style) ---

 Public Function OOo_YEARFRAC_
         (ByVal StartDate As Date, ByVal EndDate As Date) As Double
 Dim intDaysInYear As Integer
 Dim intYears As Integer
 Dim intDays As Integer
 If (Month(DateSerial(Year(StartDate), 2, 29)) = 2) Then
     intDaysInYear = 366
 Else
     intDaysInYear = 365
 End If
 
 If (Format(StartDate, "mmdd") <= Format(EndDate, "mmdd")) Then
     intYears = Year(EndDate) - Year(StartDate)
 Else
     intYears = Year(EndDate) - Year(StartDate) - 1
 End If
 
 If (intYears = 0) Then
     intDays = EndDate - StartDate
 Else
     intDays = EndDate - _
           DateSerial(Year(EndDate), Month(StartDate), Day(StartDate))
     If (intDays < 0) Then
         intDays = intDays + intDaysInYear
     End If
 End If
 
 OOo_YEARFRAC = intYears + CDbl(intDays) / CDbl(intDaysInYear)
 End Function
 


[ Link to here ]

cf.  Act/Act(AFB) (or Act/365L)   AFB : Association Francaise des. Banques
-- ISDA document [Page 3]   Actual/Actual ( AFB ) --

 Under the AFB method, the denominator is either 365 (if the calculation period
 does not contain 29th February) or 366 (if the calculation period includes 29th
 February) - where a period of longer than one year is involved, two or more
 calculations are made: interest is calculated for each full year, counting backwards
 from the end of the calculation period, and the remaining initial stub period
 is treated in accordance with the usual rule.When counting backwards for this
 purpose, if the last day of the relevant period is 28th February, the full year should
 be counted back to the previous 28th February unless 29th February exists, in which
 case, 29th February should be used.
 



[ Link to here ]

YEARFRAC ( Basis:1,Act/Act )  [ Google SpreadSheet ]

After examining YEARFRAC(Act/Act) of Google SpreadSheet, a calculation method of
Google SpreadSheet is ISDA method.

-- ISDA method --
(Step-1)   Separate a period every one year (from January 1 to December 31).
(Step-2)   About each years,divide [the period days of the year] in [annual days
                (365 or 366) of the year].
(Step-3)   The total of the result of Step-2 of each years becomes the calculation result.

--- Implementation logic (Google SpreadSheet) ---

 Public Function Google_YEARFRAC _
         (ByVal StartDate As Date, ByVal EndDate As Date) As Double
 Dim i As Integer
 Dim dblFrac As Double
 Dim SDate As Date
 Dim EDate As Date
 
 dblFrac = 0
 For i = Year(StartDate) To Year(EndDate)
     SDate = DateSerial(i, 1, 1)
     EDate = DateSerial(i + 1, 1, 1)
     If (SDate < StartDate) Then
         SDate = StartDate
     End If
     If (EDate > EndDate) Then
         EDate = EndDate
     End If
 
     If (Month(DateSerial(i, 2, 29)) = 2) Then
         dblFrac = dblFrac + (CDbl(EDate - SDate) / 366#)
     Else
         dblFrac = dblFrac + (CDbl(EDate - SDate) / 365#)
     End If
 Next i
 
 Google_YEARFRAC = dblFrac
 End Function
 





[ AddinBox Home (Japanese) ]    [ English Home ]


AddinBox ( K.Tsunoda in Japan ) CopyRight(C) 2008 Allrights Reserved.