Auto return value based on period of time

[stevembe]

Not sure how best to approach this one so hopefully somebody can help. I have a spreadsheet that calculates length of service using the DATEDIF function which displays as years, months e.g. (29 years, 4 months) which is calculated from a Start Date in Column H and a TODAY in Column I, What I need to achieve is for the adjacent cell (K2) to return an allowance entitlement based on the length of service. So:</SPAN>

Length of Service Entitlement (what K column should automatically return)</SPAN>
If 0 to 6 months return 6 weeks
If greater than 6 months to 12 months return 12 weeks</SPAN>
If greater than 12 months to 3 years return 24 weeks</SPAN>
If greater than 3 years to 5 years return 36 weeks
If greater 5 years return 48 weeks</SPAN>

It may be that I should do the calculations from the start and today date columns but not sure. Most grateful for any help.</SPAN></SPAN>

 

[AlexandraT]

Is this what you need?

=IF(DATEDIF(H2,I2,"M")<=6,"6 weeks",IF(DATEDIF(H2,I2,"M")<=12,"12 weeks",IF(DATEDIF(H2,I2,"M")<=36,"24 weeks",IF(DATEDIF(H2,I2,"M")<=60,"36 weeks","48 weeks"))))

 

[stevembe]

Is this what you need?

=IF(DATEDIF(H2,I2,"M")<=6,"6 weeks",IF(DATEDIF(H2,I2,"M")<=12,"12 weeks",IF(DATEDIF(H2,I2,"M")<=36,"24 weeks",IF(DATEDIF(H2,I2,"M")<=60,"36 weeks","48 weeks"))))

That's perfect Alexandra, thank you so much for taking the time to help me out.

 

[AlexandraT]

My pleasure.
Alex