Nested IF Formula
 

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",I F(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.

Nested IF Formula
 

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.


"Lori1218" wrote:


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",I F(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.




--
Lori1218

Nested IF Formula
 

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,""))))


--
Biff
Microsoft Excel MVP


"Lori1218" wrote in message
...

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",I F(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.




--
Lori1218

Nested IF Formula
 

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

"Lori1218" wrote in message
...

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",I F(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.




--
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 want to put in their hire date and then have the sheet update their accrual rate. I put in their hire date and then I created a formula that show when their accrual rates should change. it shows the month, day and year of the new accrual rate. What i need it to do is when the accrual rate should change I need it to fill in a box with the new accrual rate . I have everything setup except for the changing accrual . I have 12 boxes with each month in the box. I then want to put the formula next to each month and have it figure out the accrual when it comes to that 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/2013 (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" wrote in message
...

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",I F(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.




--
Lori1218 [/quote]

THANK YOU !!!!!That worked..