I am really starting to wish I hadn’t suggested our sickness recording spreadsheet could be improved…
I am fairly confident with Excel, and can see the possibilities – and will attempt VBA if necessary (I suspect it may be but I’m not really familiar with it)

I have a spreadsheet to track sickness with a summary sheet as the first worksheet and subsequent worksheets for each month. Sickness is recorded as 1, 0.5 or 0 where 1 is a full day sick, 0.5 is half day and 0 is a non-working day (some staff work shifts). The monthly sheets are set up with columns A & B holding staff names, C with a formula to display the total value from range D:AH (this being each day of the month).
The summary sheet pulls values from Column C for each month, and also has the summary data from the previous year. I was intending to directly input the values from last year as the old sheet is not directly compatible. The year runs from April to March.

I need to be able to flag the following 3 'triggers':
Sick for 5 or more working days in a row
Sick for 3 working days over the last rolling 3 month period
Sick for a total of 5 or more working days over a rolling 12 month period

I am thinking that the monthly worksheets could have conditional formatting where if 5 of the previous 7 cells are not blank the text turns red (some staff work weekend so they may or may not need to be included) – but due to the varying shifts and rolling requirements I am tying myself up in knots!

I would really appreciate any help anyone can give me – unfortunately my ambitions don’t match my expertise – yet! Hopefully this gives enough of an idea of my aims – if not please let me know.

Thank you in anticipation