Conditional Formatting deadline date based on completion date

Question

Hello, I need help in figuring this one,

I have a spreadsheet which includes a column for due date of a particular task (starting at D3) and an adjacent column showing the actual completion date (starting at G3). A different task is on each row.

I want to apply conditional formatting to the cells in D column (deadlines) so that they will turn RED on the following conditions:

1. if there is no date entered into the column G (date completed)
2. when a date entered into the G column (date completed) on the same row, if the date in G is after on the date in D.

Then cells in D column (deadlines) will turn WHITE/NORMAL color when the date entered into G column (actual completion), is between the dates in C Column (date assigned) and D Column (deadline).

I'm really hoping that you can help me. Thank you very much

Answer 1

Hi Mark Rafael Mendoza,

Thanks for your post in Microsoft Community.

Create two rules to meet your needs. 

Select your data range; in this case, let's assume your data range is G3:G100, then go to the Conditional Formatting button above the Ribbon > New Rule. 

In the new window, choose 'Use a formula to determine which cells to format,' and in the formula input box, enter the following formula to accommodate the situation where there is no date entered in column G (Date Completed): 

=AND(ISBLANK(G3), NOT(ISBLANK(D3)))

In the Format button, change it to red, click OK to save the settings, and that completes the creation of one rule. 

Similarly, create another rule, this time entering the following formula in the formula input box: 

=AND(NOT(ISBLANK(G3)), G3 > D3) 

Again, select red as the format. 

The scenario where the completion date falls between the assigned date and the due date, being formatted as normal color, is already covered by the aforementioned rules, so you do not need to create a separate rule for it.

I hope the solution I shared works for you, and I look forward to your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Thomas,

It worked on the sheet that I was working on.

I really appreciate your help. Thank you so much!