Assuming you have an earlier version of Excel, I think the simplest method
would be to use VBA and a UDF (user defined function).
Also, some further testing reveals that the method I proposed doesn't always
calculate correctly.
However, your specifications lead to some largish differences. And I wanted to
be sure they were correct before proceeding.
For example, assume we are evaluating someone today 10 Feb 2009
If hired on 3 Jan 2005 he will have accrued 40 days
Hire date + 90 days --> 3 Apr 2005
10 monthly days accrued
Hire date + 12 months --> 3 Jan 2006
Accrual start 1 Jan 2007 so another 10/yr for 3 years.
But if hired on 31 Dec 2004 his hire date + 12 months is now 12/31/05 so he
will start his annual accruals a year earlier, and will have 55 vacation days
by today.
Is this correct?
--ron
Oops, the person hired 31 dec 2004 will have 54 vacation days.
Here is a method that uses a bunch of helper columns (rather than VBA) to avoid
the nesting limitations of pre-2007 versions of Excel.
Set up your sheet as follows:
Set up a table and NAME it VacTbl. It should look like:
Yrs Accrued Multiplier
0 0 10
3 30 15
9 120 20
To NAME the table, select the table, then select the Define Name menu option
and enter VacTbl in the NAME box.
------------------
Then enter the following:
$A$1: AsOfDate
$B$1: HireDate
$C$1: HireDate+90d
$D$1: HireDate+12m
$E$1: Yr1Accrual
$F$1: Months
$G$1: Years
$H$1: Accrued Vac
A2: Date to be evaluated
B2: Date Hired
C2: =B2+90
D2: =MIN(DATE(YEAR(B2),MONTH(B2)+{12,13},DAY(B2)*{1,0}))
E2: =DATE(YEAR(D2-1)+1,1,1)
F2: =SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(A2,C2)&":"&MIN(A2,E2-1))))=1))
G2:
=SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(E2,A2)&":"&A2)))*MONTH(ROW(INDIRECT(MIN(E2,A2)&":"&A2)))=1))
H2:
=MIN(10,F2)+VLOOKUP(G2,VacTbl,2)+(G2-VLOOKUP(G2,VacTbl,1))*VLOOKUP(G2,VacTbl,3)
-------------------------
You can hide columns C:G if you want, once you confirm that the calculations
are proper.
--------------------------
Notes:
The above can be done using a UDF in Visual Basic, but this algorithm should
run faster as spreadsheet functions (mostly because of the array calculations),
and that might make a difference if you have lots of employees.
In versions of Excel prior to 2007, this method will not handle dates that are
after 5 June 2079. Hopefully, by then, if this is still being used, the Excel
version will have been upgraded.
--ron
