6 month rolling attendance calendar

[mdobbins]

I am trying to track our employees attendance on a 6 month rolling attendance calendar. I am having a hard time getting the attendance points to roll off after 6 months. I attached a sample of the formula I am trying to use.

[ExcelSmarts.com]

Hi mdobbins,

I took a look at your spreadsheet. I do not see a formula for 6 month attendance... I am looking in cells N2 and N3. Can you please elaborate some?

Thank you,
Mike

[mdobbins]

I apologize, I do not have a formula. I am just starting this process and only have the formula to add up the attendance points. Now I am needing to have something to make them fall off after 6 months.

[ExcelSmarts.com]

Ok, I think I understand. For example, right now John Smith has 2.5 points due to being late, etc. So currently, he has 2.5 points total for the past 6 months (the past 6 months being the 11 days in the spreadsheet). You would like to continue adding these points to the total, but on July 1, it should no longer add points from January 1 and on July 2, it should never add points from January 1 or 2 (so you have a 6 month rolling average of the points). Does that sounds right?

[mdobbins]

That is correct.

[ExcelSmarts.com]

mdobbins,

I believe you can do this by taking these two steps:

1) Make a field (say cell "O2") that has today's date minus 183 days by putting this formula in: =TODAY()-183

2) Then put this formula in N2 to calculate your points for the past 183 days: =SUM((COUNTIFS(B2:L2,"=LT", B1:L1,">"&O2)/2),(COUNTIFS(B2:L2,"=CO", B1:L1,">"&O2)),(COUNTIFS(B2:L2,"=LE", B1:L1,">"&O2)/2))

This basically uses COUNTIFS (plural), so that in addition to checking that the values are "LT" or "CO" or "LE" before adding points, it also checks that the corresponding date is within the past 183 days.

As for the 183 days, I don't know of a better way to calculate what is considered the past 6 months.

[ExcelSmarts.com]

If you want to check that formula is working with your current data, you can type over the formula you put in cell "O2" with a date in your list, like 1/7/2010 and see that the points total changes accordingly.