Vacation Entitlement based on full years of service and odd vacation year

Maybe something like:

=IF(start_date - DATE(2017, 5, 1)>395, INDEX({10,15,20,25}, MATCH((start_date - DATE(2017, 5, 1)) /365.25, {1,4,10,18}, 1)) , MIN( INT((start_date - DATE(2017, 5, 1)) /30), 10))

"start_date" to be changed to the appropriate cell reference for the given employee's start date.

And that DATE(2017, 5, 1) thing might need to be changed to either a reference to another cell referencing the start date of the fiscal year (if you have one), or maybe a test that updates it to the most recent May 1st, using an IF buried in there.

Also that's going off "30 days", not "1 month" -- it's a little different than what exactly you asked for, but fiddling with count of full months is a lot more complex.

And you might want to feed the lookup with a table, instead of having arrays embedded in the formula. More stable that way.

Thanks for the response ben_hensel but I guess I'm not sure what I'm doing. I tried fiddling with your formula to make it work in my sheet but got a return of 2 instead of 20.
I'm not super awesome at excel development and I appreciate the help. I can plug in a network table around here so i guess this is my job now.

See what you think of the attached.

Looks like I reversed the order of operation LOL

try this:

=IF($B$4 - C10>395, INDEX({10,15,20,25}, MATCH(($B$4 - C10) /365.25, {1,4,10,18}, 1)), MIN( INT(IF($B$4>C10, $B$4 - C10, C10 - $B$4) /30), 10))

EDIT:
I realized it didn't handle someone starting after the beginning of the fiscal year correctly (eg, if someone starts on June 12th of 2018 and you add them then), so I had to add an IF into the MIN to handle that.

Originally Posted by bferd

Employees who have a start date between May 2nd and April 30th accrue 1 day for each month of continuous service to a maximum of 10 days paid time off.

Don't know if I'm reading this correctly....

Do you mean that employees who have less than 1 years service get the 1 day per month, otherwise they get the days per full year as you have defined?

If so try this formula

=IFERROR(LOOKUP(DATEDIF(C9,B4,"y"),{1,5,10,18;10,15,20,25}),MIN(10,DATEDIF(C9,B4,"m")))

So close, both of you! You are amazing.

Yes, you are reading that correctly daddylonglegs.

I did a bit of testing with both of your formulas and got the full years (under 5 years, less than 10 years, etc) working properly. There was a bit of tweaking, although I can't explain why. I had to change {1,4,10,18} to {1,5,11,19} in both of your formulas.

ben_hensel

=IF($B$4 - C9>395, INDEX({10,15,20,25}, MATCH(($B$4 - C9) /365.25, {1,5,11,19}, 1)), MIN( INT(IF($B$4>C9, $B$4 - C9, C9 - $B$4) /30), 10))

daddylonglegs

=IFERROR(LOOKUP(DATEDIF(C10,B4,"y"),{1,5,11,19;10,15,20,25}),MIN(10,DATEDIF(C10,B4,"m")))

Where both have trouble is under 10 months. If I set "today's" date (C2) to May 1, 2017, and set the hire date for the same day, the entitlement shows 10 days. I can get the entitlement days to go down to zero if I set the hire date 11 months and 1 day into the "future" past whatever C2 is set at.


See attached for example..

I did some more fiddling and got a little closer. Everything works for the next vacation year (2018-2019), but not if it is the current vacation year (2017-2018). The month accrual seems to not like something. Check out the attached files and see what you think.

Note: I did a bit of iferror wrapping just to blank out some #VALUE cells on my actual worksheet.

I'm confused. Is accrual based on time until May 1st of the current Fiscal Year, like you said in your first post; or is it based on time until today?

No, that doesn't answer my question.

Like....

Normally vacation days are calculated once, at the start of the year, and you get them for the year. Then at the start of the next year, your vacation is recalculated again for that year.

Let's say someone starts in June.

In May, 3-and-a-change years after they start, they have 10 days of vacation.

Do they get another 5 days available from the June of 4 years after they start?

What if they started, I dunno, April 28th, and literally cannot use up those 5 days before the end of the fiscal year? Do you roll them over? Do you have to buy them back, as per whatever the employment agreement, or what?

Originally Posted by bferd

Each employees vacation entitlement is determined on May 1st of each reference year.

This makes me think "calculated on May 1st for the rest of the year."

Originally Posted by bferd

Where both have trouble is under 10 months. If I set "today's" date (C2) to May 1, 2017, and set the hire date for the same day, the entitlement shows 10 days. I can get the entitlement days to go down to zero if I set the hire date 11 months and 1 day into the "future" past whatever C2 is set at.

But this sounds like you recalculate vacation allotment every day. Especially when you mentioned arbitrarily fiddling the lookup to "get it to match".