Sick Leave Tracking

MamaT2 · Mar 21, 2016

I've searched the threads and do not find one that fits what I am trying to accomplish. I am trying to set up a worksheet that will track sick and personal leave. Here is the scenario for sick leave. Our teachers are granted 10 new sick leave days (80 hours) a year at the end of the year they may carry forward 4 of the unused new days (32 hours) from the year (they become banked days) into the next year, up to a maximum of 30 days (240 hours) in any given year. Time used is first subtracted from the new days for the year, when those are exhausted the banked days are used. There is no other form of accrual for days. (I've used days for ease, I will convert to hours when updating payroll in Quickbooks)

Employee Smith:
Begins the school year with 20 sick days, (10 new for the year and 10 banked); he uses 3 days during the year. All are subtracted from the new days, leaving 7 days. At the end of the year 4 of 7 days left can be carried over (banked) for the next year. Employee starts the next year with 14 banked (10 existing plus 4 carried) and 10 new for a total of 24 days.
Employee Jones:
Begins the school year with 30 sick days, (10 new for the year and 20 banked); he does not use any days. At the end of the year 0 of 10 days left can be carried over (banked) for the next year because the bank if full. Employee starts the next year with 20 banked (20 existing plus 0 carried) and 10 new for a total of 30 days.
Employee Burns:
Begins the school year with 20 sick days, (10 new for the year and 10 banked) employee uses 8 days. All are subtracted from the new days, leaving 2 days. At the end of the year 2 of 2 days left can be carried over (banked) for the next year. Employee starts the next year with 12 banked (10 existing plus 2 carried) and 10 new for a total of 22 days.
Employee Thomas:
Begins the school year with 20 sick days, (10 new for the year and 10 banked) employee uses 15 days. All are subtracted from the new days, leaving 0 days and 5 days are subtracted from the bank. At the end of the year 0 days left can be carried over (banked) for the next year. Employee starts the next year with 5 banked (5 existing plus 0 carried) and 10 new for a total of 15 days.
What is the best way to set up a sheet to incorporate these parameters? I appreciate any help. Thank you.

J.Ty. · Mar 21, 2016

Hi, welcome to MrExcel!

Assuming that the number of sick days at the beginning of the year is in A1, the number of used sick days is in B1, then the numer of sick days at the begining of the following year is

Code:

=MIN(30,10+MIN(A1-B1,A1-6))

The formula assumes that the employee never uses more sick days than available. Formally, I assume that A1 is greater or equal than B1.

Does it help?

J.Ty.

MamaT2 · Mar 22, 2016

YES! Thank you! That has worked perfectly.

J.Ty. · Mar 22, 2016

You are welcome!

J.Ty.

Sick Leave Tracking

MamaT2

New Member

Excel Facts

J.Ty.

Well-known Member

MamaT2

New Member

J.Ty.

Well-known Member

Similar threads

Forum statistics

Share this page

Sick Leave Tracking

MamaT2

New Member

Excel Facts

J.Ty.

Well-known Member

MamaT2

New Member

J.Ty.

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock