Accruing Sick Time Formula


I need an easier way to track our accrual of sick time and hoping someone can help me with a formula. Our law states "Employees accrue 1 hour of sick time for every 30 hours worked or 1-1/3 hours for every 40 hours worked a week." Any help would greatly be appreciated.




Excel is the tool you need for this but asking for a formula is putting the cart before the horse. Your first step is the design of the worksheet. Actually, you probably need two. The first one is a simple log.

  1. A column for the employee's name
  2. A column for the date
  3. A column for the number of hours of sick time on that date

The second sheet has a summary.

  1. A column for the employee's name
  2. A column for the accumulated total of sick time taken
  3. Perhaps a column calculating any excess
  4. Perhaps further columns to calculate the financial consequence of excess

This summary sheet needs fields in its header which allow the definition of the period for which the summary is made: Start date, End date.

You may have more requirements. They should all be considered and implemented in the design. Once the design is perfect you will find that the first sheet just captures data and you may think of ways to do so more efficiently. Some Excel tricks or formulas may help, such as getting the two lists of employee names from a single source.

It's the second sheet which must draw data from the first to compress them into a report that will require formulas. You can't start thinking about them before the design of both sheets is finalised and no one can design a formula for you without precise knowledge of the worksheet designs. Post your workbook to get help.


Answer the Question

You must create an account to use the forum. Create an Account or Login