Vacation accrual calculator

Collapse -

This is not about Excel

by TobiF · 10 years ago In reply to Vacation accrual calculat ...

In order to answer this question, one would need to know what rules and laws apply in your case.

How many days a week do you work?
Do you count vacation in workdays or calendar days?
Should accrual be accounted per workday, calendar day, or maybe first per week or month (and then adjusted for differences)? (And do you have any normed quantity of workdays per month or year?)
Is it possible to get extra vacation days if one works additional days?

Collapse -

Work days

by kwright1822 · 10 years ago In reply to This is not about Excel

They work 5 days a weak. 8 hours per day. We are paid every week.

Collapse -

Still information lacking

by TobiF · 10 years ago In reply to Work days

You answered the 3 easy questions out of appr. 10 questions.
I know how, in general, you'd handle this in Sweden. But even if I'd be able to find information about the labor law in your country, I really don't know about the specific details in your contracts.

So, if you want help creating that model in Excel, you need to give the needed input.

Oh, and it's quite possible that when you sort this information out for yourself, then you'll be able to create the model on your own. It's not that hard. But if you need help with formulas for calendar dates etc, then we can help here.

A hint, though:
If we assume that a year has 52 weeks (which is not completely true) and are going to count public holidays etc as worked time, then you could say that 4 holiday weeks should be accrued over (52-4)=48 weeks (i.e. one work week gains 1/12 vacation week.
And, correspondingly, 5 holiday weeks would be earned over 47 weeks.

Collapse -

Contract

by kwright1822 · 10 years ago In reply to Still information lacking

1. They receive the equivalent of 1 day of vacation for each 10 shifts worked but not to exceed 20 days of vacation.
2. They receive the equialent of 1 day of vacation for each 9 shifts worked but not to exceed 25 days of vacation.
Shifts worked or paid for shall be counted in computing vacation credits.(Holiday are included). A shift worked is 8 hours per day. 40 hours per week.

Collapse -

Now it's much easier.

by TobiF · 10 years ago In reply to Contract

=MIN(INT((TotalShiftsCurrentMonth+TotalShiftsToPreviousMonth)/VacationEarningFactor)-PreviouslyEarned;INT(MaxDaysPerYear-PreviouslyEarned))

So, how to read this?
The MIN statement returns the lowest value of what is accrued during current month and how many days are still left before you hit the max number of earned days for a year.

Total shifts up to previous month is needed to carry over "unused shifts" from previous month. Therefore the days accrued during the current month is calculated as total days earned to date minus days already given before.

Next, you can replace VacationEarningFactor and MaxDaysPerYear with a VLOOKUP function.

Of course, in this formula, I used names instead of links and values, but you should be able to link these appropriately.

Collapse -

Spreadsheet

by kwright1822 · 10 years ago In reply to Now it's much easier.

How would you setup an excel spreadsheet for this?

Collapse -

Probably not...

by TobiF · 10 years ago In reply to Spreadsheet

I'd probably not set such a structure up in Excel, but rather in some kind of database, at least if I were to keep track of employees, bank accounts, salaries, saved vacation days, worked hours etc.

But, directly to your question. I'd probably use the formula just the way I showed you. With the help of "named ranges":

Name verticals, one cell wide. When you refer to the range name like I did here, you'll get the value of the corresponding cell from the row of the formula.

So, go read about named ranges. They're useful.

Software

Question