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 String) As 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
