Complex If Formula to accumulate weeks of vacation earned.

Hello all,

I need help, please.

I'm trying to construct a complex if formula that will accumulate the amount of weeks of vacation earned for employees. As per the guideline at the top right of the attached spreadsheet, after one year of service, they earn one week. After two years (and every year thereafter) they earn two weeks of vacation every year.

I've already established a column for the years of service in column E using a simple TODAY-Hire date function. I'm trying to tie in that "Years of Service" column so that it is automatic with the numerous employees we are currently hiring and I need to keep track of.

Thank you for your help with this.

Nic

Hi
Try this formula:
=IF(E9<1,"",IF(AND(E9>=1,E9<2),"1",IF(AND(E9>=2,E9<5),"2",IF(AND(E9>=5,E9<10),"3",IF(E9>9,"4")))))
Also suggest you format column E to show 2 decimal places, this will explain the results of the formula better.
Good luck.
Tony

Your formula in col E rounds the number of years up or down as you decided to go for 0 decimals.
In some cases ( like cell E10) this seems to give a wrong answer.
To be More precise, you can use =DATEDIF(C9,TODAY(),"Y")