Conditionally Format cells based on relationship to a max value

[Kingsof82]

Hi,

I'm looking to identify through conditional formatting, when a task has been performed, but has been performed late.

Staff supervisions need to be performed every three months, and for each staff member, I have the dates of these entered into rows. I'm looking to fill a cell orange, when the date in that cell, is more than 3 months and 1 week later than the highest value in previous cells.

I've found a solution to use:
=[cell]-MAX([ range])>97

However, I would prefer to use 3 months and one week instead of 97 days as this would make more sense to those overseeing the department. I'd guess this would incorporate YEAR(), MONTH()+3, DAY()+7 but as yet I haven't been able to successfully incorporate this into a MAX value formula.

Any advice would be greatly appreciated,

Thanks

 

[Fluff]

You could use something like

=A2>EDATE(MAX(D2:J2),3)+7