Thread: Calculating accrual of sick days

I have a 2010 Attendance Sheet in Excel (sample attached). It's organized into five sheets: a year-to-date summary sheet, plus one sheet for each quarter. It tracks vacation, doctor's appointments, workshops, sick days, etc. On the Summary sheet there is a formula that calculates how many vacation days and sick days are left.

Sick days accrue at a rate of .5 day per month, unless a sick day was taken in that month. So, if you take a sick day in January, you don't earn your additional .5 day.

In column R on the Summary sheet, I have a formula to calculate the accrual of sick days. In column U, I'm trying to develop a formula that will look at each month, for each employee and calculate whether or not .5 day should accrue for that month. I have gotten myself totally confused. Before I tried to allow for months when the .5 day would not accrue, I just had to caculate the number of months elapsed to date and divide by two.

I then created EmployeeMonth named cells, e.g., JanuarySmith, FebruarySmith and so on, to calculate IF JanuarySmith has an "i" no .5 day will accrue for employee Smith. Now I am stuck . . .

Any suggestions would be much appreciated!

G'day

Does this work for you ???

It not the best but it works

Cheers

ps i hear Ratcat is THE guru on sickdays

Thanks so much for your help. The formula achieves what I'm looking for.

Oh dear. Spoke too soon. When I try to include all 12 months of the year, it tells me that I've included "too many arguments for this function." It let's me include arguments up to August. How do I include September to December for each employee?

Suggestion.......

Split the formula into two cell

Eg 6 months in one cell and the other 6 months in the other cell then total the two cells together

or use excel 2007 and beyond.

Cheers

RC