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?

 

[crazydragon84]

Re: Help requested with the yearfrac function

1) i would create a mapping table (something like below):
Years: Vacation Weeks:
1 0
2 1
3 1
4 1+1
5 2
6 2
.
.
.
9 2+1
10 3
.
.
.

2) =vlookup(Rounddown(Now() - Start Date), above table, 2,0)

Does this work?

 

[gdrowell]

Re: Help requested with the yearfrac function

i get a #N/A when I enter

=VLOOKUP(ROUNDDOWN(NOW() -A1,0), K1:L27, 2,0)

 

[crazydragon84]

Re: Help requested with the yearfrac function

A1 is start date? sorry you should do YearFrac(Now()) - YearFrac(Start Date)

 

[gdrowell]

Re: Help requested with the yearfrac function

Column A is the Start date column.

Should it look like this:

=VLOOKUP(ROUNDDOWN(YearFrac(Now() - A1,1)), K1:L27, 2,0)

Still getting an error

 

[crazydragon84]

Re: Help requested with the yearfrac function

er sorry =VLOOKUP(ROUNDDOWN(YearFrac(Now() ,A1,1),0), K1:L27, 2,0)

 

[barry houdini]

Re: Help requested with the yearfrac function

As you only need whole years DATEDIF might be better, e.g.

=VLOOKUP(DATEDIF(A1,TODAY(),"y"),K1:L27,2,0)

​

 

[crazydragon84]

Re: Help requested with the yearfrac function

that works too. So i get 1+1 until 8/10/18 then get 2 from 8/11/18 for someone that has 8/11/13 start date.

 

[gdrowell]

Re: Help requested with the yearfrac function

Right, however i only want it to show 1+1 only in year of the 5th year until anniversary date.

Example: Start Date 08/11/14

In 2018 Should show 1 week
In 2019 Should show 1+1 week (1 week + 1 week after the anniversary date in the 5th year), then shows 2 weeks after anniversary date
In 2020 should show 2 week (Completed 5 years of service)

 

[crazydragon84]

Re: Help requested with the yearfrac function

That's what it does for me? this is how it shows up on mine (by replacing Now() with end date):

[TABLE="width: 350"]
<colgroup><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD][/TD]
[TD]End[/TD]
[TD][/TD]
[TD]Shows[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/10/2018[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/11/2018[/TD]
[TD][/TD]
[TD]1+1[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/10/2019[/TD]
[TD][/TD]
[TD]1+1[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/11/2019[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/10/2020[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2014[/TD]
[TD][/TD]
[TD="align: right"]8/11/2020[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]