On Tue, 10 Feb 2009 23:22:52 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 20:41:44 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote:

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao

What version of Excel do you have?

It should work in 2007.

It won't work in earlier versions because of nesting limitations.
--ron

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(I NDIRECT(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