Vacation days from date of hire
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
"Ron Rosenfeld" wrote:
On Thu, 5 Feb 2009 07:32:32 -0800, srctr
wrote:
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?
Yes. I'd just put it into a cell and NAME the cell.
Here's the modification for the 10+years:
=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)+
MAX(0,(YEAR(AsOfDate)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-9)*5
--ron
|