Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Last edited by Lori1218 : August 31st 08 at 12:19 AM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
|
|||
|
|||
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] |
#6
|
|||
|
|||
THANK YOU !!!!!That worked..
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regarding NESTED IF formula | Excel Discussion (Misc queries) | |||
Nested if formula | Excel Discussion (Misc queries) | |||
Nested formula help | Excel Worksheet Functions | |||
Nested Formula help | Excel Discussion (Misc queries) | |||
Help with nested formula | Excel Discussion (Misc queries) |