bandrade8613
New Member
- Joined
- Dec 13, 2012
- Messages
- 6
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!
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!
