Multiple criterion date conditional formatting

[CADavies]

Hi,

I really need some help with this because I have a sheet that is currently only set up for two criteria and is displaying red when it shouldn't however I don't know how to address the following requirements.

This is for a training matrix where some courses are Mandatory or Optional

First level

1. if course optional and field is blank then no do not highlight the cell
2. if course mandatory and field is blank then highlight red

Second level

3. if course is optional and the date in the field is greater than today by 60 days do not highlight the cell
4. if course is optional and the date in the field is within today by 60 days highlight the cell yellow
5. if course is optional and the date in the field is equal to or less than today by 1 highlight the cell red

Third level

6. if course is mandatory and the date in the field is greater than today by 60 days do not highlight the cell
7. if course is mandatory and the date in the field is within today by 60 days highlight the cell yellow
8. if course is mandatory and the date in the field is equal to or less than today by 1 highlight the cell red

Your help is greatly appreciated.

Kind regards,

Cat

 

[arthurbr]

Hi and welcome
which XL version are you using?

 

[James006]

Hi,

Could you describe the sheet's structure ... :wink:

1. In which cell is shown the Optional / Mandatory feature ?

2. In which cell is supposed to be the date or blank ... if my guess is correct ...?

Despite your three levels ... can you confirm you do end up just with two colors : Yellow and Red ...

 

[CADavies]

Answers:
1. each column has a training course name in row 3 i.e. E3, F3, G3.
Under this in row 5 is the number of years between retraining requirements i.e. 1, 2, 3 or 4
Under this in row 7 is frequency of the course i.e. yearly or once
Under this in row 7 is the number of days the frequency years determine i.e. 2 x years = 760 days
Under this in row 8 is the training requirement i.e. mandatory or optional

Each participant is named in a row starting at A11

The heading options dictate which cells are ok to be blank i.e blank is fine when the training is optional, however should highlight red when blank if the training is mandatory

If the training is mandatory however only has to be trained once and there is a date in the column then it the date should never be highlighted. If the training is mandatory and the date is within 60 days of today's date then it should highlight yellow. If the date is past today's date it should highlight red otherwise it is not highlighted at all as it is compliant.

Hope that makes sense

 

[James006]

Hello again,

My understanding is the range of cells which need to be conditionally formatted starts in cell E11 ...

and extends down to the row where you have the name ... and extends to the left for as many training courses you have in Row 3 ...

An initial test would be to add the following formula as your first rule :

AND(E11="",E$8="Mandatory")

Let me know if it helps ...

 

[CADavies]

Your understanding is correct.

I tried the code in conditional formatting with the expectation that a blank cell of a mandatory course would turn red and it didn't. Am I doing something wrong because I'm trying to use this formula in a google excel sheet?

 

[James006]

Hello,

Sorry ... but Google Sheets is a different animal ... :wink:

 

[arthurbr]

@CADavies
Now I know why you did not bother to answer my question....