Conditional formatting based on previous 85 days...

[UrbanBeard]

Assistance please - If one of my staff is absent I will put an x in today's column at the side of their name, I need that cell to auto highlight in red if they have had an x in the previous 85 days, this does not include weekends.

 

[Chrisdontm]

Some cell references would surely help...

You would want to use a COUNTIF checking for the letter "x".

If it is greater than 0 within the range of the last 85 days...

Someone can help you if you say where the dates are, and how they are listed...

 

[UrbanBeard]

Thank you for your response and I have sorted. Using workdays across the top and staff names and blank rows alternating down the side. The formula in the cell above the cell that gets the X which counts the previous 85 days, conditional formatting in the cell itself looking for >0 in the cell above and an X in the cell.

 

[Chrisdontm]

Assuming your row with the "x" inside it, is row 6, and assuming you started using this formula to look back 85 days is in column CX,

Then highlight cells CX5:DX100, then go to Conditional Formatting and choose: "New Rule"

Then choose: " Use a formula to determine which cells to format"

Then in the box put this formula:

=AND(CX6="x",CX5>0)

The choose: " Format " , and then choose a color, then click "ok", and "ok" again.

Then go back into Conditional Formatting, and choose: " Manage Rules "

And change the range to how far you really want it to go to...

Of course change all these references to what your actual cell references are...

I hope this helps...