Hi Mark!
YEARFRAC is definitely in error with calculations of more than a year.
Myrna Larson and I played around with this some time ago. On the way
through we discovered that there were errors with DATEDIF as well
where the "yd" argument is used. In the case of YEARFRAC the main
problem was the peculiar algorithm for determining the number of days
in a year; they take the average number of days in the years spanned
by the starting and ending dates. It could be said that this is not an
error but a question of definition although independently Myrna an I
both came up with an approach that fits in with most people's logic.
In the case of DATEDIF and the "yd" argument it's a plain old stuff
up.
Here's the gory details:
Analysis ToolPak has a YEARFRAC function which has apparent
attractions for calculating the difference between dates in terms of a
year and fractions of a year. The difficulty of such calculations is
that years have varying lengths and approximations of 365.25 produce
errors because 365.25 may not be the average length of year involving
any two dates.
YEARFRAC has the syntax:
=YEARFRAC(start_date,end_date,basis)
YEARFRAC has alternative third arguments for its calculations. Outside
specialist financial instrument basis arguments 2 and 4, the two that
are of initial interest are Argument basis = 1 (Actual / Actual) and
Argument basis = 3 (Actual / 365). The numerator of these fractions is
the number of days and the denominator is the assumed length of year.
Where YEARFRAC basis = 1 and the dates are more than a year apart it
can be shown that YEARFRAC uses as its divisor the average number of
days in the years start_date to end_date inclusive of the start_date
and end_date years irrespective of where in those years the start_date
and end_date falls.
But where the dates are 1 year or less apart it uses either 365 or 366
depending upon whether either year is a Leap Year and where in the
year the start_date and end_date fall relative to 29-Feb.
That makes YEARFRAC basis 1 difficult to replicate as a formula and it
produces non-exact years of service for all anniversary dates except
the first.
I can't see how YEARFRAC can be used for years of service with basis 1
which strikes me as the most likely candidate. YEARFRAC with basis = 3
similarly fails to produce exact years for all anniversary dates
except the first. This is important in the context of finding a method
that can be used for legal entitlements and any alternative must have
consistency. The method must also be one that is capable of being
translated to an acceptable definition in terms of how it is
calculated or lawyers will have a ball and we will be that ball. (Not
a bad mixed metaphor that one!)
So I go for a formula approach and hope that I can get a clear up
definition on the way.
Calculating whole years is easy:
=DATEDIF(A1,B1,"y")
I've tested this and it always seems to give the right answer and it's
the simplest approach.
Calculating residual days is not so easy!
=DATEDIF(A1,B1,"yd")
returns some annoying 1 day errors for example:
=DATEDIF("10-Apr-2003","9-Mar-2005","yd")
returns: 333
=DATEDIF("10-Apr-2003","10-Mar-2005","yd")
returns: 335
There's got to be something wrong there!
Also:
=DATEDIF("15-Sep-2002","14-Mar-2004","yd")
returns: 181
=DATEDIF("15-Sep-2002","15-Mar-2004","yd")
returns: 181
There's got to be something wrong there as well!
So to calculate the days since the last anniversary date I use:
=B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))
I've tested this and it doesn't appear to produce any errors.
So we have an algorithm for years and days between dates that yields
the correct answer:
=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
This in itself is a very useful formula as it is different from the
one that we usually use and which produces errors for dates such as
those used above.
Determining the numerator for calculating the fraction of a year for
the residual days is a debatable point. Various arguments can be put
forward for different approaches but to my mind the most logical is to
use the count of days between the last anniversary and the next
anniversary. In essence I'm saying that we ask the question, "How many
days are there between the last anniversary and the next anniversary
and what decimal fraction of those days have I used up?" Since my
formula and your UDF produce the same answers, it appears that we are
using the same logic (but not necessarily the correct one <vbg>).
To calculate this number of days, I need to calculate the date of the
next anniversary and deduct the date of the previous anniversary and
here, of course I hit the same problem of potential for error in
calculating days.
Date of Next Anniversary is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))
Date of Previous Anniversary is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))
So days between Anniversaries is:
=DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+D
ATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))
So I can now calculate the fraction of the year between anniversaries.
=(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A
1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1
,"y"),MONTH(A1),DAY(A1)))
Add that to the number of whole years:
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEA
R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
This algorithm was tested against a VBA function solution by Myrna
Larson and they checked with one another in all cases. The VBA
solution is:
Function YearDiff(ByVal StartDate As Date, _
Optional ByVal EndDate As Date = #1/1/100#) As Double
'modified 02/01/2003
Dim AnnDay As Long
Dim AnnMonth As Long
Dim AnnYear As Long
Dim ltemp As Date
Dim NextAnn As Date
Dim PrevAnn As Date
If EndDate = #1/1/100# Then EndDate = Date
'put in right order if necessary
If StartDate > EndDate Then
ltemp = StartDate
StartDate = EndDate
EndDate = ltemp
End If
'get anniversary date in ending year
AnnYear = Year(EndDate)
AnnMonth = Month(StartDate)
AnnDay = Day(StartDate)
'assume it's already occurred
PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)
If PrevAnn <= EndDate Then
'assumption that it's past was correct
'next anniversary is 1 year in the future
NextAnn = DateSerial(AnnYear + 1, AnnMonth, AnnDay)
Else
'wrong -- we calculated the *next* anniversary
NextAnn = PrevAnn
AnnYear = AnnYear - 1
PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)
End If
YearDiff = AnnYear - Year(StartDate) + _
(EndDate - PrevAnn) / (NextAnn - PrevAnn)
End Function 'YearDiff
And here's a quote from an e-mail from Myrna Larson that adds more to
the gory details of this function:
But I have an even better (i.e. worse) one:
with dates Jan 1, 1960, Jan 1, 2003, and Basis = 2; result should
be 43, but YEARFRAC gets 43.62778 !!!
With the first date 1/1/1936, the error is 0.97778!
Assuming that (perhaps) YEARFRAC is correct for periods up to 1 year,
in order to take advantage of it's Day Count Basis argument, I wrote a
new function that calculated the number of whole years from start date
to last anniversary date, used YEARFRAC to calculate the fractional
year from the anniversary to the final date, and added the 2 together.
With a starting date in the year 1936 and testing against all dates in
the year 2003, I get the same result for day count basis of 0 or 4,
but differences for 1, 2, or 3. The typical difference equated to
~0.25 days for option 1, 357 days for option 2, and 17 days for option
3 (I got that by subtracting my result from YEARFRAC's and multiplying
by 365.25).
They have sure fouled something up with options 2 and 3!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Mark Graesser said:
Hi Norm,
YEARFRAC works fine in Excel 97. If the dates are more then a year
apart it produces a whole number for the full years with a decimal
portion for the partial year.
However, DATEDIF is definetly the better bet. YEARFRAC doesn't
really handle leap years very well, and it requires loading the
Analysis Toolpack.
