Conditional formatting with 3 conditions to meet and due dates

Question

Conditional formatting with 3 conditions to meet and due dates

Ryan 0

Hi, I have been trying to search some formula where I can use conditional formatting for the below

Where Column H will format if Column B, C, & D showing "Pending Reply" (will be blank in some rows) & if date today minus date submitted (Column A) is more than 14 Days

0 comments

3 answers

Your answer

Answer 1

Henry-N 13,130 Microsoft External Staff Moderator

Hi @Ryan,

Thank you for posting your question in the Microsoft Q&A forum.

Regarding applying conditional formatting to column H, please follow the step-by-step instructions below:

Steps to apply conditional formatting

First, ensure your data is set up as described
Select cell H2 (or the first cell in column H where you want the rule applied)
Go to Conditional Formatting > Manage Rules
Click New Rule
Select “Use a formula to determine which cells to format”
Enter the following formula:

=AND($B2="Pending Reply",$D2="Pending Reply",$F2="Pending Reply",TODAY()-$A2>14)

User's image

Click Format, choose your preferred color (for example, red), then click OK

User's image

Click Apply to activate the rule

The rule checks if columns B, D, and F all contain "Pending Reply"

It also verifies whether the date in column A is more than 14 days before today

If all conditions are met, the corresponding cell in column H will be highlighted

Example

If the date in column A is 15/05/2026, the condition will not be met (not older than 14 days), so no formatting is applied
If the date is changed to 01/04/2026, the condition is met, and the cell in column H will be highlighted (e.g., in red)

Thank you again for your time and understanding. I appreciate your patience, and I’m here to help—please feel free to share any updates or additional details.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Ryan 0 Reputation points

2026-05-16T02:59:29.0166667+00:00

Hi, thank you The formula works, although, there will be cases that either B2, D2 or F2 will be blank and it will not format as intended
I have tried adding <>="", to the formula but doesn't work.
Henry-N 13,130 Reputation points Microsoft External Staff Moderator

2026-05-18T09:32:40.7933333+00:00
Hi @Ryan,

Thanks for your update that’s a great point.

To prevent the rule from triggering when none of the cells contain "Pending Reply" (for example, all are blank), you can add an extra condition to ensure at least one cell has "Pending Reply".

Please use the updated formula below:

Text=AND( OR($B2="Pending Reply",$B2=""), OR($C2="Pending Reply",$C2=""), OR($D2="Pending Reply",$D2=""), COUNTIF($B2:$D2,"Pending Reply")>0, TODAY()-$A2>14 )

With this setup:

The rule will still trigger if at least one cell contains "Pending Reply" (even if the others are blank).

It will not trigger if none of the cells contain "Pending Reply".

It also ensures that any populated cells must either be blank or "Pending Reply".

This ensures the formatting only applies when at least one of the columns contains "Pending Reply", while still allowing the others to be blank.

Hope this helps! Please let me know if you have any further questions.
Ryan 0 Reputation points

2026-05-19T00:18:38.63+00:00

Thank you so much for your assistance, will try the formula.
Henry-N 13,130 Reputation points Microsoft External Staff Moderator

2026-05-19T14:49:33.4333333+00:00

Hi @Ryan,

Please take your time.

Whenever you have an update on the issue, feel free to share it here I’m always here to help.

Thank you so much for your patience and understanding.

Answer 2

Barry Schwarz 5,676

The following formula evaluates to TRUE when when the date in A2 is far enough back and either

all three data cells contain "Pending Reply"

or

any two of them do and the third is empty

=AND(TODAY()-A2>14,LET(p,"Pending Reply",pp,(--(B2=p))+(--(D2=p))+(--(F2=p)),b,(--(B2=""))+(--(D2=""))+(--(F2="")),OR(pp=3,AND(pp=2,b=1))))

You can use it for your conditional format.

0 comments

Answer 3

Ryan 0

Hi, thank you
The formula works, although, there will be cases that either B2, D2 or F2 will be blank.

0 comments

Share via

Conditional formatting with 3 conditions to meet and due dates

3 answers

Your answer