Vacation/Sick Time Excel Spreadsheet help!

[bandrade8613]

Hi!

I'm not completely new to excel, been using it for years but for simple things. I can do some formulas but that's about it. So I have no clue how I can get this done!

I saw a thread similar to what I need but with different specifications.

http://www.mrexcel.com/forum/excel-questions/92975-calculating-vacation-sick-leave.html

At the moment we are calculating time by hand....>.> and well I need to create a spreadsheet that calculates vacation time and sick time.

Our vacation calculates by hire date/anniversary date. It also gets paid out every year so they start again back at 0 every new year
First Year = 40 hours
2nd - 4th Year = 80 hours
5th - 6th Year = 120 hours
7th year and on = 160 hours

Our employees have to work a minimum of 1800 hours a year to receive full payout of vacation time.
For Example:
Jane Doe worked 1500 hours by 12/31/2012 we would divide 1500 by 1800 to get the percentage of her time. If this was her first year working this is what it would look like.

1500/1800 = .83 = 83%
40 hours X 83% = 33.20 accrued vacation hours

I would need an additional column where I can manually enter everyone's end of year hours worked so it can correctly tell me their vacation hours balance. If this makes sense? I hope it done...

Well that was part 1.

This part is about Sick Time. For this we have union/non-union and 2 different unions that calculate things a bit differently but if I just get the formula to calculate it one way I can figure out how to calculate it the other 2 ways.

Sick Time is calculated by 2 ways

Non-Union is after 6 months of their Hire date they start accruing after 60 days. So basically in 8 months they get 8 hours of sick pay and every 60 days after is 8 hours. They can only accrue up to 10 days (80 hours)

Union after 60 days of their Hire date they start accruing ever 73 days so for the first 7.5 hours of sick pay they would have to have worked for 133 days then they receive 7.5 hours of sick pay and every 73 days another 7.5. They can only accrue up to 30 sick days (225 hours).

These are the columns that I believe I will be needing.

Column Headings:
A) Employee Name:
B) Date Hired:
C) Hours worked:
D) Vacation Hours Accrued:
E) Sick Hours Accrued:
F) Vacation Hours Used:
G) Sick Leave Hours Used:
H) Vacation Hours Balance:
I) Sick Leave Hours Balance:

I hope you can be able to figure this out. If you have any questions please feel free to ask anything and I will try to explain it as best I can. I hope I did a decent job of explaining what I needed.

Thank you for taking the time to read this and help me if you can, it's much appreciated!

 

[bandrade8613]

Oh by the way I'm using Excel 2010. Thanks again!

 

[tonydi]

Do they use their sick time and vacation time during the year, do they earn sick time and vacation time while using sick time and vacation time or are sick time and vacation time only accrued based on time worked? You are looking to compute their running balance of sick time and vacation time earned, used and avalable balance as well as make sure they have not exceeded their maximum authorizations? How many employees? Does overtime hours applied to accruing sick and v acation time?

 

[bandrade8613]

Yes they use their sick and vacation as they accrue it if they wish to use it. Vacation is accrued by hours worked and sick is accrued by time worked like i said every 60 days they get 1 sick day = 8 hours overtime counts towards vacation pay but not sick pay this is for about 500 employees

 

[Skybluekid]

Hope this is a start for you

https://www.dropbox.com/s/tor87qci0iec4xp/Vacation Sick.xlsx

 

[tonydi]

Hours worked excludes sick time and vacation - correct?

Sick leave is earned on calendar days, not work days - correct?

 

[tonydi]

Unused Sick time and vacation are paid at the end of the emplyees year(anniversary)?
How many pay periods per year?

What information can be imported from your payroll system into excel from each pay period,
you want an audit trail back to your payroll to support YTD cumulative data on your spresdsheet?

 

[bandrade8613]

Yes that is correct. Vacation is by hours worked/years worked and sick time is accrued by time worked every 60 days they get 1 sick day = 8.0 hours

 

[bandrade8613]

Yes that is correct. Vacation is by hours worked/years worked and sick time is accrued by time worked every 60 days they get 1 sick day = 8.0 hours

Vacation is paid out at the end of their anniversary year.
Sick time does not get paid out it only banks up to 10 days if they use 1 sicks day with 10 in the bank they are left with 9 and not until another 60 days in the cycle do they get another sick day.

I just need this to calculate everyone's ending year balance of sick time and vacation so I can manually enter it into our accounting system and starting 01/01/13 the system can accrue the time on it's own.

 

[bandrade8613]

Hope this is a start for you

https://www.dropbox.com/s/tor87qci0iec4xp/Vacation%20Sick.xlsx

I tried this and I had a problem where if I used a hire date of 2011 or 2012 the vacation time it gives me errors and the time for sick is wrong.

Example:

I have an employee who was hired on 04/18/2012 who is non union so she doens't accrue any sick/vacation time for 6 months so at the end if 12/31/2012 with 1054.75 hours worked so far she should have the following accruels:

Vacation - 23.60 hours
Sick - 8.00 hours give or take but atleast 8 hours

if she were to be Union she would start accruing sick time at 60 days for both vacation and sick:

Vacation - 23.60 (same as non union)
Sick - 24 hours at least

Thanks

3.983333

<tbody>
</tbody>