Yearfrac function

[gdrowell]

Hello and thank you in advance.
Ok here is my question.

I'm using the yearfrac function to determine Years of service. YearFrac works for me because if shows the years with the months/days shown in decimal.

Each employee earns a week of vacation as follows:

2 years of service - 1 week
5 years of service - 2 weeks (1 week + 1 week after the anniversary date in the 5th year)
10 years of service - 3 weeks (2 weeks + 1 week after the anniversary date in the 10th year)
15 years of service - 4 weeks (3 weeks + 1 week after the anniversary date in the 15th year)
20 years of service - 5 weeks (4 weeks + 1 week after the anniversary date in the 20th year)
25 years of service - 6 weeks (5 weeks + 1 week after the anniversary date in the 25th year)

So,
YearFrac(Now(), Start date cell, 1) is the function i'm using to determine years of service. The Employee Start date is 08/11/2003, then in 2018, they will earn their 4th week of vacation after their anniversary date. How do i show "3+1" weeks in 2018 and then show "4" weeks 2019 and every year until the next change?

 

[gdrowell]

Re: Help requested with the yearfrac function

I ended up using a looooong nested if statement:

=IF(D2="","",IF(AND(B2>=25),"6",IF(AND(B2<25,B2>=24.1),"5+1",IF(AND(B2<24.1,B2>=20),"5",IF(AND(B2<20,B2>=19.1),"4+1",IF(AND(B2<19.1,B2>=15),"4",IF(AND(B2<15,B2>=14.1),"3+1",IF(AND(B2<14.1,B2>=10),"3",IF(AND(B2<10,B2>=9.1),"2+1",IF(AND(B2<9.1,B2>=2),"2",IF(AND(B2<2,B2>=1.1),"1+1",IF(AND(B2<1.1,B2>=0),"1","0"))))))))))))

To shorten up, i will look into the datedif statement as well. Thank you for your insight. I greatly appreciate it.