Nested IF Formula

[Lori1218]

I am having a problem creating a formula . what I am trying to do is
working off a changing date to see what accrual rate is for vacation
time. My todays date is in C2. Employees accrue at different rates per
so many years
Vac Accrual 6.25(F1) 9.38(G1) 12.5(H1) 15.63(I1)
Hire Date> 6/1/199(F2) 7/8/1997(G2) 7/29/2003 (H2) 7/16/2013 (I2)


Today is> 8/30/2008
=IF(F2>=MONTH(C2),"6.25",IF(G2>=MONTH(C2),"9.38",IF(H2>=MONTH(C2),"12.5",15.63)))

I tried this formula and I am way off. I have never done a nested if
function. Could some please help.

 

[JLatham]

It actually looks like you've put it together properly. However in the
testing you need to test for the month in F2, G2, and H2 as (just to show the
start)
=IF(Month(F2)>=Month(C2),F1,
(note that I also picked up 6.25 from F1 instead of using test "6.25").

But I'm still not sure that's going to work because you would still just be
comparing the month of hire to the current month, not comparing number of
months they have as tenure. I'm thinking you may want to use another formula
on row 3 to calculate the # of months they've been employed
A formula to use with column F, for F3 would be:
=(YEAR(NOW())-YEAR(F2))*12+MONTH(NOW())-MONTH(F2)
Note that it's going to give you a negative number for that guy you aren't
hiring until 2013.

Ok, so with number of months since hire date in row 3, you can change your
formula to something like this (assumes accrual rate changes after 12 months
and at 12 month intervals, with 6.25 being given for any tenure under 12
months)

=IF(F3<12,F1,IF(F3<25,G1,IF(F3<37,H1,I1)))
Put any employee's hire date into F2 and it should show you the proper
accrual rate for them in the cell with the formula.

Hope that helps some.

 

[T. Valko]

Not sure what you're trying to do. My best guess:

=IF(F2>=C2,F1,IF(G2>=C2,G1,IF(H2>=C2,H1,IF(I2>=C2,I1,""))))

 

[Roger Govier]

Hi

I think we need more information.
Clearly the rate of vacation days, relates to having been employed for an
ever increasing time period.
I would set out a table in A1:B4 as follows

Months | Days Vacation
0 | 6.25
24 | 9.38
48 | 12.5
60 | 15.63

Change the values in column A to match the time frames you use.
Then with Date in F2
=VLOOKUP(DATEDIF(F2,TODAY(),"m"),$A$1:$B$4,2)

 

[Lori1218]

Hi, The following formula works until I change my hire date
=IF(F2>=C2,F1,IF(G2>=C2,G1,IF(H2>=C2,H1,IF(I2>=C2,I1,""))))


What I am trying to do is created a calander for each employee. I wan
to put in their hire date and then have the sheet update their accrua
rate. I put in their hire date and then I created a formula that sho
when their accrual rates should change. it shows the month, day an
year of the new accrual rate. What i need it to do is when the accrua
rate should change I need it to fill in a box with the new accrual rat
. I have everything setup except for the changing accrual . I have 1
boxes with each month in the box. I then want to put the formula nex
to each month and have it figure out the accrual when it comes to tha
month and insert the correct amount of time accrued.

Vac Accrual 6.25(F1) 9.38(G1) 12.5(H1) 15.63(I1)

Hire Date> 6/1/2007(F2) 7/8/1997(G2) 7/29/2003 (H2) 7/16/201

(I2)


Vacation Due Accrued
January 15.63 15.63
February 15.63 15.63
March 15.63 15.63
April 15.63 15.63

 

[Lori1218]

THANK YOU !!!!!That worked..

;717128']Hi

I think we need more information.
Clearly the rate of vacation days, relates to having been employed for
an
ever increasing time period.
I would set out a table in A1:B4 as follows

Months | Days Vacation
0 | 6.25
24 | 9.38
48 | 12.5
60 | 15.63

Change the values in column A to match the time frames you use.
Then with Date in F2
=VLOOKUP(DATEDIF(F2,TODAY(),"m"),$A$1:$B$4,2)

--
Regards
Roger Govier

I am having a problem creating a formula . what I am trying to do is
working off a changing date to see what accrual rate is for vacation
time. My todays date is in C2. Employees accrue at different rate per
so many years
Vac Accrual 6.25(F1) 9.38(G1) 12.5(H1) 15.63(I1)
Hire Date> 6/1/199(F2) 7/8/1997(G2) 7/29/2003 (H2) 7/16/2013 (I2)


Today is> 8/30/2008
=IF(F2>=MONTH(C2),"6.25",IF(G2>=MONTH(C2),"9.38",IF(H2>=MONTH(C2),"12.5",15.63)))

I tried this formula and I am way off. I have never done a nested if
function. Could some please help.