Excel: How to calculate accrued vacation time for employees in California?
I’m creating a spreadsheet for my boss of the staffs sick/vaca time. But staff keeps asking me when they will have more time. How do I calculate that?
2 Responses
Sharmaine
15 Feb 2010
HEATHER
15 Feb 2010
You don’t have a payroll company to take care of this?
Let’s say Bob gets 2 weeks of vacation per year. Let’s assume Bob is full time and gets paid every other Friday, and you accrue vacation time as you go.
2 weeks is 10 days off; or a total of 80 hours
Paid every other Friday results in 26 paydays per year
26 days divided by 80 hours means Bob accrues 3.076923 hours per paycheck.
So you have a "starting balance" column. Then you can have a "year to date accrued" column, which would be 3.076923 x the number of pay periods processed so far. Then you would have a "hours used" column and would have the total of what Bob has taken off this year.
Of course you would need to adjust the hours per pay period accrued if someone was there longer and getting 3 or 4 weeks. Or if you get paid on the 1st and 15th you only have 24 pay periods in a year, not 26.
On it sites are good top links for decisions of your problem http://sitefinance2010.notlong.com/5AAnzRu