I need to track how many times employees are absent. They are allowed 5 unexcused absences in any 365 day period. My spreadsheet looks like this -
where the dates are in Col Aand the number of absences are in Col B
<tbody>
</tbody>
So on Jan 1, 2018 my count would be 2. But on Jan 3, 2018 my count would only be 1, as the absence for 1/2/2017 would have dropped off because we are using a rolling 365 day period. The previous spreadsheet owner used OFFSET, MATCH, and named ranges. I hear INDEX could work. I don't do VBA.... is there an easy way to do a COUNTIF with a 365 day rolling range? Thank you
where the dates are in Col Aand the number of absences are in Col B
Date | Joe absent |
1/1/2017 | |
1/2/2017 | 1 |
1/3/2017 | |
1/4/2017 | 1 |
1/5/2017 |
<tbody>
</tbody>
So on Jan 1, 2018 my count would be 2. But on Jan 3, 2018 my count would only be 1, as the absence for 1/2/2017 would have dropped off because we are using a rolling 365 day period. The previous spreadsheet owner used OFFSET, MATCH, and named ranges. I hear INDEX could work. I don't do VBA.... is there an easy way to do a COUNTIF with a 365 day rolling range? Thank you