How to choose a specific color in conditional formatting based on a formula

Hi all,

In the attached excel file - DM PR Availability sheet, Column C changes to Not Available based on the below criteria.

1. If the value in column D is Do Not Disturb

2. If the value in Column E is Out Of Office

3. If the Review Status for an ID in Records sheet is Pending

4. If the ID in Records sheet status contains 2 or more Review Status as Completed in a single day.


Retaining all the above criteria, I would like to add a conditional formatting to the Column C in DM PR Availability sheet as below.

If a person has 1 completed Review Status in a single day, then the Available status should be highlighted in Yellow (as shown in the file).

Can someone help with the formula.

Pass for sheets = 123

Thanks in advance.

Hi Glenn,

Thank you for your response.

The below formula has already been used in order to identify if an ID has 2 completed review status in column S of DM Records sheet , so that the column C in DM PR Availability sheet shows as Not Available.

Formula:

Please Login or Register  to view this content.

=IF(OR(D4="Do Not Disturb", E4="Out Of Office",COUNTIFS(Table5[ID2],'DM PR Availability'!A4,Table5[Review Status],"Pending")>0,COUNTIFS(Table5[ID2],'DM PR Availability'!A4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed")>1),"Not Available","Available")

Formula:

Please Login or Register  to view this content.

Ans 1. Similarly, what i want is - in column S of DM Records sheet ,if an ID has 1 completed under "Review Status" in a single day, that is today then column C in DM PR Availability sheet should show as Available in Yellow color. However, the next day since the date has changed, it should be as its currently displaying i.e. Available highlighted in Green.

Ans 2. Yes, the information exist in column S of DM records.

Do let me know if anything further. Thanks.

So on 21 Feb - in column S of DM Records sheet ,if an ID has 1 Completed in today's date, then column C in DM PR Availability sheet should show as Available in Yellow color. However, the next day on 22 Feb, it should be as its currently displaying i.e. Available highlighted in Green.

There's no specific date. Instead it should be today's date.

Perfect JeteMc. This is exactly what i wanted.

Thanks a bunch. Really appreciate your help and efforts.

Also thank you Glenn for your efforts and time.