# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Formula for Calculating (Accrual) Vacation Time

## usfscoop

I need to develop a tracking spreadsheet (for 12 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters: 

Employees who have worked less than 3 years with the company:
- 2.91 hours biweekly
Employees who have worked more than 3 years but less than 8 years with the company:
- 4.38 hours biweekly
15 years and over with the company:
- 6.41 hours biweekly

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

----------


## clownfish

Attached is something to get you started--
Just enter employee names in place of the Employee1, Employee2, etc.
-enter start date for each employee
-enter hours used for each employee on rows 5 through 104 (if you need more space, just edit the SUM portion of the formula in the Vac_avail row to include more, for instance, ...SUM(B5:B2004) would give you 2000 spaces
-edit the formula in Vac_Rate to give a rate to the poor souls who have worked at least 8 but less than 15 years (you didn't mention these people in your post, so I just made up a rate of 5.5 hrs./14 days--change the 5.5 to whatever)
-the formulas assume that no vacation time is lost at the end of a year.
-entering a negative number in the Vac_Used rows will, in essence, award more time to that employee which may be useful-- these will show up in red
-if an employee has used more than allowed, their vac_avail will show up in red also

----------


## clownfish

actually- small mistake; use this one

----------


## clownfish

actually, it occurs to me that you will need a slightly more sophisticated formula for vacation accrual, because as it currently is, when a person passes the employment milestones (3 yrs, 8yrs, etc), the vacation they previously accrued (before the milestone) gets inflated also, which isn't what you want (though it would be nice for the employees).  Don't have time at the moment, but perhaps someone else does?  Or you can give it a try yourself.

----------


## steve102680

I am in need of some serious help. I have looked in the four corner of the earth and cannot find a solution to my problem. 

I am trying to create a spreadsheet to calculate vacation accural for my employee's. 
I have reapeatedly tried to create formulas that are not doing what I need them to do. 

Here is what I am needing:

Employee gets 2 hours per pay (bi-weekly) for 0 - 1year employed

Employees that are employed 1 year and beyond accrue 3 hours per pay (bi-weekly)
the max is 2 weeks or 80 hours that is allowed no matter how long you are employed. 

Also vacation time does not expire at all. 

This is of course all based on there hire date. 

I am very new to the formula world so I will need a idiots explanation please.

----------


## SHAWNIE

Please help.

I am a salaried employee paid twice a month.  I am in desperate need of a spreadsheet to track my own time.  I accrue 1.25 hours each month and receive 6 personal days each year.  I do okay with spreadsheets but really need some help here.  I would like a spreadsheet that would include formulas to recalculate my time when it's used.  I don't have a problem with entering the 1.25 each month.  My vacation time does not expire.

Any assistance would be greatly appreciated.  Thanks in advance!  :Smilie:

----------


## arthurbr

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## dangerme

> actually- small mistake; use this one



hello this spreadsheet was super helpful for me but have one question. i actually need to change the formula from the TODAY to a specific date, 3/15/2013. i only need to figure out the pto time accrued from the before that date. also, for my company, everyone gets the same amount of pto accrual of 3.07 hours per pay period except for one employee who gets 3 weeks, i have not figured out the pay period breakdown. 

help please, how do i change the Today part of the formula to a specific date?

thank you

----------


## protonLeah

dangerme
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## lizf

We offer PTO to parttime employees. 2 Weeks after one year 3 weeks after five years. I have a parttime employee who works 3 days a week 7 hours per day. She is a 10 month only employee. She also did not earn her 3rd week until Sept. 1. How would I calculate how much time she has accrued?

----------


## Fotis1991

* lizf*

Welcome to the forum.

Pls see post$9!

----------


## lizf

Thanks I'm new to the site and not sure I posted in the correct area. Just trying to get some helpful information, I am new in this position. Trying to figure out this parttime vacation accrual.

----------


## iccg

This was extremely helpful to me.  Thank you so much.  Made my day.

----------


## KitKatB

When we have employees leave in the middle of the year or when we have employees start in the middle of the year - how do I calculate how many vacation days they get?  Is there a simple excel formula or a simple function to figure this out?

Generally we use: For example, an eligible staff employee (6 months or more of service) with a standard workweek of 5 days per week accrues two (2) standard workweeks of vacation per year: 5 days per week x 2 weeks per year (10) ÷ 12 months per year = 8.5 hours per month. - 

But working this out, I get easily confused with all the numbers.

Any help is appreciated.

----------


## Pepe Le Mokko

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## indygoof2007

Ok... Need help!!  I know the extreme basics of formulas in excel.  I have a growing need for a better way to keep up with my employees vacations.

I have just over 120 employees.... They earn 1 week of vacation after one year (from their start date) of working.  The vacation time does not rollover and I have varying lengths of weekly amounts (i.e. 40 hours, 35, 30, 20, etc...)  I just need to be able to keep track of the dates they take the vacations and for the amount to deduct once they take a day/week of vacation.  HOW do I accomplish this??

----------


## FDibbins

indy, welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## aiv73

I am actually just trying to keep up with my PTO time. According to our hand book it has nothing regarding PtO time, accrued rate for Part time employees. I want to figure out for each pay period by the hours worked how many PtO hours I have earned.

----------


## dctrp

***started a new thread..sorry

Help please. I'm trying to create a tracker. I need to calculate how many accrued SL per month will each employee have if we are given 7 SL in a year. I can't seem to figure out the formula. Thank you.

----------


## cbomb920

Please Help!  I am trying to create a tracker.  I need to calculate how many accrued PTO per year each employee will get.  I am struggling to figure out the formula.
This is how it is explained.
	PTO is not accrued between the employees hire date and December 31st of the employees hire year.
	1st full calendar year after hire, PTO is not accrued. 
	2nd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 4 days (32 hours).
	3rd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 6 days (48 hours).
	4th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 7 days (56 hours).
	5th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 8 days (64 hours).
	6th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 9 days (72 hours).
	7th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 10 days (80 hours).
	8th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 11 days (88 hours).

----------


## FDibbins

> Please Help!  I am trying to create a tracker.  I need to calculate how many accrued PTO per year each employee will get.  I am struggling to figure out the formula.
> This is how it is explained.
> 	PTO is not accrued between the employees hire date and December 31st of the employees hire year.
> 	1st full calendar year after hire, PTO is not accrued. 
> 	2nd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 4 days (32 hours).
> 	3rd full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 6 days (48 hours).
> 	4th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 7 days (56 hours).
> 	5th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 8 days (64 hours).
> 	6th full calendar year after hire, PTO is accrued at a rate of 2 days (16 hours) per month, not to exceed 9 days (72 hours).
> ...



*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------


## cbomb920

created by mistake

----------

