Hi Mark!
There are some problems with YEARFRAC where dates are more than a year
apart. It's an algorithm thing.
If the problem is one of calculating whole years of service then
=DATEDIF(A1,B1,"y")
appears OK and doesn't produce errors at any time as far as my testing
goes.
For exact calculation of years and fractions life becomes more
difficult:
Myrna Larsen and I did some work on this earlier this year.
I came up with this formula replacement:
Age in years and fractions of a year:
=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(YEAR(A1)+
DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
A complete run down on the problem including Myrna's VBA UDF
equivalent follows.
Problem with YEARFRAC Option 1 for fractions over a year
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)+DAT
EDIF(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(YEAR(A1)+
DATEDI
F(A1,B1,"y"),MONTH(A1),DAY(A1)))
This algorithm was tested against a VBA function solution by Myrna
Larsen 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:
Assuming your hire date is in A1 you can use the following:
in A2: =YEARFRAC(A1,TODAY(),1)
In A3: =IF(B1>15,20,IF(B1>7,15,IF(B1>2,10,IF(B1>1,5,0))))
The formula in A2 will give you the years of service. The formula
in A3 will give you the earned vacation days.
Good Luck,
Mark Graesser
(e-mail address removed)
----- Anderson wrote: -----
Hello Everyone,
I need help with a formula. I need to calculate a hire date
and time off earned and the time off earned will be based on how long
they have been employed. Here is an example:
Date of Hire Vacation Earned (if 1 yr = 5 days off; If 2-6
years = 10 days off; If 7-14 years = 15 days off; If 15 years = 20
days off)
