Need help creating function to calculate vacation time

[xancthus]

To start off, I have excel 2003. I am the new HR assistant and i am in charge of keeping track of and calculating how much vacation and sick time an employee gets. I want to create a formula that will automatically figure it out for me. The way my spreadsheet if setup, is that for every year an employee has worked here, they get a higher amount of vacation time accrued. Every two weeks, and employee gets 1.54 hours of vacation time if they are full time employees. That is for the 1st year of employ. During the second year of service, the employee gets 2.15 hours of vacation time every two weeks. And the amount increases every year.
What I want the function to do, is to automatically increase the vacation time every two weeks. If it could do this based on the hire date that would be great. So, if I input their hire date on one cell and have it know to add 1.54 to their vacation time every 14 days on a different cell, this would save me hours of labor. Is this possible?

 

[Scott Huish]

Hi, welcome to the board!

What is the amount that it increases every year, does this increasing amount have a cap on it at some point and is there a maximum amount of vacation time that can be accrued?

 

[xancthus]

re:

Every year it increases a different amount.
1st year 1.54 hours per pay period (2 weeks) 40 hrs max
2nd 2.15 hrs 56 hrs max
3rd 2.77 hrs 72 hrs max
4th 3.39 hrs 88 hrs max
5th 4.00 hrs 104 hrs max
6th and up 4.62 hrs 120 hrs max

 

[likewisecc]

You could try something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2				Hours/2Wks>>	1.54	2.15	2.77	3.39	4.00	4.62	4.62	4.62	4.62	4.62
3	Name	HireDate	toDate	YrsService	1	2	3	4	5	6	7	8	9	10
4	Katie	5/1/2003	89	2	40	49
5	Frank	8/2/1996	795	9	40	56	72	88	104	120	120	120	75
6	Joe	7/2/2000	329	5	40	56	72	88	73
7	Anna	3/1/2005	2	1	2
Sheet1

This will caculate how many vacation hours employees have accumulated to date, based on hire date.

This is rather quick and dirty, though; others may have a better solution.

 

[likewisecc]

Oh, is there a max carry-over from yr-to-yr? How do you plan to enter hours used to date?

 

[xancthus]

Associates are only allowed to carry over 40 hours of unused vacation time.
The way I'm tracking used time is from an excel template I got from office online. It's called employee absence tracker. here is the link so you can see what I'm using.

http://office.microsoft.com/en-us/templates/TC062561561033.aspx

I plan to make the actual vacation tracker that your showing me as the first page in the spreedsheet. Then the second page shows the total vacation alloted and used, then the rest of the sheets are the months.