Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't
be to difficult to add the 10+ get 20 days of vacation, but seeing the
formula it might be. I am assuming the As of Date could be now or today or a
set date like the first of the year?

Thanks, I will give this a try.

--
cao


"Ron Rosenfeld" wrote:

On Wed, 4 Feb 2009 13:06:22 -0800, srctr
wrote:

I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then
will accrue 1 vacation day at the beginning of the first of the following
month and one additional vacation day for each month after for a maximum of
10 days per year.

Thereafter they receive 10 days on January 1st after their
first 12 months of service. 4th thru 9th they will recieve 15 days on
January 1st.

Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then
15 days on Jan 1 after that?

What happens during the 10th year?

Hopefully there is a simpler formula, but this seems to work for the parameters
you've mentioned.

The two relevant dates are the "HireDate" and the "AsOfDate" The latter is
the date being evaluated for how much vacation has been earned.

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)
--ron