Vacation and Sick Tracking

**Olympus** · 08-12-2010, 12:12 PM

I'm trying to find some way that I can independently track my own vacation and sick time and double check what my employer says I have. I have absolutely no experience with Excel, so bear with me. Fortunately, the vacation and sick time are fairly straightforward.

1st year vacation acrues at 5 hrs per month with a maximum accumulation of 168 hours.
1st through 3rd year sick time acrues at 5 hrs per month with a maximum accumlation of 216 hours.

2nd - 3rd year vacation acrues at 10 hours per month with a max of 168 hours.

4th -5th year vacation acrues at 13 hours per month with a max of 168 hours.

4th-5th year sick acrues at 7 hours per month with a max of 408 hours.

I don't mean to burden everyone, but I need some way to track the vacation and sick hours and be able to subtract hours I've used so that it updates my total. Can anyone help me?

**ChemistB** · 08-12-2010, 12:30 PM

Are you looking at starting a fresh spreadsheet for each year or do you want to track within a single spreadsheet over multiple years? Are you allowed to carry over time from year to year and if so, are there limitations?

**Olympus** · 08-12-2010, 12:35 PM

I can do whichever is easier, either make a new sheet every year use the same one.

Yes, I can carry over balances as long as it doesn't go over the maximum time I listed. I typically use most of my time in a year. I will carry a couple days worth, 20 or so hours, into the next year in case something happens. But I don't forsee me hitting the maximum time allowed.

**ChemistB** · 08-12-2010, 12:47 PM

Do accruals calculate at the beginning or end of the month, i.e.
Jan 1, do you have 5 hrs sick time already and 10 hr on Feb 1st OR do you have 0 on Jan 1 and 5 on Feb 1st?

Also, does vacation and sick time accrue from hire date or some other formula?
When you say 1st year, does that mean after 1 year employment or upon hiring?

**Olympus** · 08-12-2010, 08:30 PM

Good question. I would say at the end of the month, probably. My sick and personal leave get updated when I receive my last paycheck of the month. So probably the end of the month to be on the safe side.

**ChemistB** · 08-13-2010, 11:02 AM

Okay, I'm attaching a workbook.
I created a table for years worked versus sick and vacation time on sheet 2 and called it "Table"
To get Vac time earned = would be
(Months of year -1) * hrs earned per month (depending on how many years of service)
VLOOKUP($C$2,Table,4) looks at years of service (C2) and returns from Table what's in the 4th column (hrs/month). Then add carry over (C3). However it can't be more than the maximum so we put everything inside a MIN statement and it looks like this;

=MIN(VLOOKUP($C$2,Table,5),(MONTH(TODAY())-1)*VLOOKUP($C$2,Table,4)+$C$3)

Sick time is similar.

Then to subtract what you've already taken
=SUMIF($B$8:$B$200,"V",$C$8:$C$200) sums up the hours of vacation you've taken this year.
Any questions?
Does this work for you?

**Olympus** · 08-14-2010, 08:49 PM

That's going to work excellent! Thanks a ton!

**[email protected]** · 01-12-2011, 04:16 PM

I want to build a spread sheet to track my cumulative available vacation time based upon my office's accrual rate of one hour earned per seventeen hours worked. Thanks in advance for your help.