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!
Bookmarks