Excel spreadsheet to calculate sickness absence in 12 month rolling period

[KB2015]

Hi

I am looking for help to create a spreadsheet that keeps a record of employees sickness absence over a rolling 12 month period and formula that will highlight when they go over 20 days in a rolling year YTD.

At the moment I have a spreadsheet that has around 100 employees (listed Vertically) and a cell for each date (horizontally) which keeps ongoing. On a day they are absent a "1" is put in to the cell for that day against that employee.

What I would like is a simple cell formula that will trigger (change cell colour, etc) when the employee reaches 20 days absence in a rolling 12 month period.

Many thanks in advance.

[AliGW]

So what you will need to do is use a combination of SUMIFS, TODAY() and EDATE.

Something like this:

=SUMIFS(sum_range,date_range,"<="&TODAY(),date_range,">"&EDATE(TODAY(),-12))