# OMG Trying to Calculate Vacation and Sick Time

1. ## OMG Trying to Calculate Vacation and Sick Time

I am dyin' here...

I've attached a sample sheet I started 4 hours ago...need to calculate how much time everyone has in sick and vacation time. Don't believe they want it to go back to their start date. Simply calculate for the year but WHATEVER...just want to get it DONE!!!

Here are the parameters

Policy for Sick Time:

Reg FT employees accrue sick leave at the rate of 5 days per year (.41666 days per month).
Leave is used at a minimum of 4 hours

Vacation policy:
• employees working here under 10 years earn up to a max of 10 vacation days a year accrued at a rate of .8334 days per month
• after 5 years employees earn up to a max of 15 vacation days each year accrued monthly at the rate of 10 hours per month
• After 10 years the employee may earn up to a max of 20 vacation days each year, accrued monthly at the rate of 13.3334 hours per month

Thanks to anyone who can help me with this!!!

2. ## Re: OMG Trying to Calculate Vacation and Sick Time

You seem to be mixing days and hours. Wouldn't it be simpler to keep everything in hours? If so, how many hours per day does your group work?

3. ## Re: OMG Trying to Calculate Vacation and Sick Time

Question on rate policies: Do the increased rates begin on their 5 and 10-year anniversaries, or do the rates change on January 1st of the year they would reach that anniversary?

4. ## Re: OMG Trying to Calculate Vacation and Sick Time

Thanks for taking the time to reply...I'm just taking it from the handbook. They base things on an 8 hour day (5 day work week)

5. ## Re: OMG Trying to Calculate Vacation and Sick Time

Anniversary day

Thanks for the assistance

6. ## Re: OMG Trying to Calculate Vacation and Sick Time

Hi and welcome to the forum

for the Sick Time, try this, copied down and across...

=IF((((YEAR(\$A3)-YEAR(B\$2))*12)+(MONTH(\$A3)-MONTH(B\$2)))*0.416666<=0,0,(((YEAR(\$A3)-YEAR(B\$2))*12)+(MONTH(\$A3)-MONTH(B\$2)))*0.416666)

7. ## Re: OMG Trying to Calculate Vacation and Sick Time

Thanks for the welcome but I don't understand where I paste this based on the spreadsheet I included with my original question. Forgive me. They've really thrown me with this project and it turns out they have never been able to figure it out themselves so they decided to put ME to the test.

8. ## Re: OMG Trying to Calculate Vacation and Sick Time

I'm giving it a shot. I have questions about the policies:
1) Do they accrue gradually over time (so a new employee starts with 0 days), or do they get time pre-loaded at the beginning of the year?
2) If based on accrual, does someone stop accruing if they reach the cap?
3) Can vacation days roll over from year to year, or do they get paid out/lost on Dec 31st?

9. ## Re: OMG Trying to Calculate Vacation and Sick Time

The solution that FDibbins put in works, but you have to change the months in A3:A4 to actually be Date/Year (like 1/2013, 2/2013, etc).

Then you can put his solution into B3, mouse over the bottom right-corner of the cell (so it turns into a plus), and then "drag" the formula all the way down to B14. Then you can drag the column's formula over to H3:H14.

Caveats: It is giving you the total summation of all accumulated sick leave. It is not showing the balance, because nowhere on the sheet does it indicate what time they have taken.

I'm working on the vacation part...I did a similar spreadsheet on here in the past, but even looking at, I can't remember how to use half the functions I used then! So...still working on it.

Interestingly, your sheet gave Jen and Tina full sick benefits for the month of February, even thought they only worked part of it. FDiddens' formula gave them none for February, b/c it calculated they had worked 0 months. :P

10. ## Re: OMG Trying to Calculate Vacation and Sick Time

sorry, thanks for the catch, jkray, i did change them in my version, but forgot to mention it

11. ## Re: OMG Trying to Calculate Vacation and Sick Time

Originally Posted by jkray622
I'm giving it a shot. I have questions about the policies:
1) Do they accrue gradually over time (so a new employee starts with 0 days), or do they get time pre-loaded at the beginning of the year?
2) If based on accrual, does someone stop accruing if they reach the cap?
3) Can vacation days roll over from year to year, or do they get paid out/lost on Dec 31st?

Yes there is a cap
No vacation/sick does NOT rollover - and it is based on their anniversary date NOT Dec 31

Thanks so much for helping me out!!

12. ## Re: OMG Trying to Calculate Vacation and Sick Time

Originally Posted by jkray622
Question on rate policies: Do the increased rates begin on their 5 and 10-year anniversaries, or do the rates change on January 1st of the year they would reach that anniversary?
Rates chg on their anniversary dates ... not Jan 1

There are currently 1 users browsing this thread. (0 members and 1 guests)