Looking for a formula to calculate how much vacation time will be accrued

Hi All,

I am trying to create a worksheet for vacation hours for my company. Essentially I want it to take the Hire date of the employee and calculate how many hours they will accrue in the current calendar year. Our vacation time accrues per pay period (26 pay periods in a calendar year). The breakdown of time earned is as follows:
Upon completion of 6 months of service the employee receives 40 hours of vacation. No vacation is accrued during that first six months. Following the 6 month anniversary date the employee will begin accruing additional vacation time each pay period. Then every January everyone is reset back to 0 and begins to accrue time again .

6-12 months - 40 hours
1 year - 5 years - 80 hours
5 years - 10 years - 120 hours
10 years or more - 160 hours

I appreciate any help you can provide. I am terrible with dates on excel.

Originally Posted by daddylonglegs

So if somebody hits a significant anniversary during the year (like a 5 year anniversary) will the hours change during the year? For example if you hit the 5 year anniversary in April will you go from getting 80/26 hours per pay period to 120/26 per period?

Do you round the hours at all?

When are the pay periods start dates

Yes so someone with an anniversary mid year would accrue more per pay period after the anniversary date. I round the hours up to the nearest hour. The pay period starts on Saturdays with the next one starting 9/20/14. Thanks for the questions, all stuff I should have thought about!

I've attached a spreadsheet if that helps.

Originally Posted by daddylonglegs

OK this formula should give you vacation hours for 2014 based on hire date in B3

=ROUND(SUMPRODUCT(LOOKUP(DATEDIF(B3,DATE(2014,1,11-14)+ROW(INDIRECT("1:26"))*14,"m"),{0,6,12,60,120;0,40,80,120,160}))/26,0)

That's based on this first pay period of the year starting on January 11th 2014, with hours calculated for each period based on tenure at the start date of the period.

Hours are summed for all periods then rounded (use ROUNDUP rather than ROUND if you want to always round UP).

Of course that's not allowing for any hours in the payperiod which starts in the previous year and goes in to the current one (and the last pay period goes in to next year)

Thanks so much for the help. I would try to fix my issues myself but this looks like nothing I've ever dealt with. Few questions:
It seems to round each half separately rather than at the end when there is an anniversary mid year. For example in Row 6 The first 12 pay periods he accrues 55.38 hours and the last 14 he accrues 86.15. Added together that would be 141.53 which rounded should come to 142. Instead the formula is rounding each individually, 55.38 to 56 and 86.15 to 87 and then adding to 143. Is there a way to adjust that?

Also for people who have their first anniversary in the current year are not calculating correctly. The day after their 6 month anniversary they get 40 hours. And then every payroll after the accrue time base on an additional 40 hours. As an example row 4 should calculate out to 68. (40 after the 6 month anniversary and an additional 18 payrolls she accrues and additional 27.69 hours rounded to 28).

Thanks!

Originally Posted by daddylonglegs

The rounding is done at the end - the discrepancy is because my formula is giving 11 pay periods at the lower amount, not 12 - perhaps the formula should refer to the last payperiod start date of the previous year, i.e. 28th December 2013, that will give the correct result



OK, I missed the part about paying 40 hours after 6 months. For a hire date of 7th October 2013, then, would there not be a 40 hour payment, then 13 payments at 40/26 and the last 5 at 80/26 (because they also hit the 1 year anniversary in October?) I make that a total of 75.

This revised formula would give those results

=ROUND(SUMPRODUCT(LOOKUP(DATEDIF(B3,DATE(2013,12,28)-14+ROW(INDIRECT("1:26"))*14,"m"),{0,6,12,60,120;0,40,80,120,160}))/26,0)+(YEAR(EDATE(B3,6))=2014)*40

The red parts would need to be changed for each year - 2014, obviously should be the year in question and the DATE part should be the last pay period start date of the previous year

OK perfect. Now the only issue I have is people with Hire dates in 2014 are returning a #NUM! error? Other then that everything else is correct. Good catch on the 6 month and 1 year in the same year!

Ok I need to revise this after spending a few hours with HR. I guess the wording they use is for legal purposes but mathematically it makes problems on my
spreadsheet. So I think this just got more complicated. Basically we do accrue vacation beginning day 1. So the revised breakdown is this:

Hire date - 5 years - 80 hours
5 years - 10 years - 120 hours
10 years or more - 160 hours

Also HR suggests I calculate based on hours accrued per day to make it cleaner since pay periods will span two calendar years.

Where it gets complicated is for new employees. New employees are not allowed to use Vacation in the first 6 months of employment, therefore if their 6 month anniversary falls anywhere on or after the last 5 days of the year the accrued time will roll over to the next year.

So in the case of row 4 here total time for 2014 should be 99 Hours (86 days worked in 2013 * .22 hours accrued per day = 18.85 hours plus 365*.22 for 2014 = 80 hours for a grand total of 18.85+80 = 98.85 which rounds to 99)

Sorry this is completely different than my original post, but I guess the way they write the book vs actually calculate things is completely different!

Maybe I should start a new thread since this is so different then my original request? I'm thinking calculating based on days in the year should make this easy, and may be able to accomplish with a nested if function. I'm not good with dates though so any help would be appreciated. I also need to figure out a way to have the formula know when to carry over the time earned from last year and when not to (if their 6 month anniversary lands on the 5 last days of the year. Thanks in advance!