Advice appreciated: Conditional formatting based on dates combined with an IF query

**Guber1** · 12-08-2021, 02:05 PM

Hi - so, as you can hopefully see from the attached, I want to be able to mark projects "Red", "Amber" and "Green" in column H, depending on whether they're ahead of time, on time or past a given deadline (Columns E and F).

To complicate matters, I also want to be able to mark those projects as "Green" if the project status (column D) is marked as completed.

I think I'm sort of halfway there with the formula:

=IF(E3>=TODAY(),"G",IF(F3>=TODAY(),"A","R"))

But what I haven't been able to work out is how to change that text value "G", "A" or "R" into a fill colour.

Nor have I been able to work out how to link that final fill colour in column H to the status of the project (column D).

Any excel experts out there able to provide me with guidance? Much appreciated if so.

**etaf** · 12-08-2021, 02:16 PM

you an setup three rules in conditional formatting

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
H3:H100 - extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=H3="G"

Format [Number, Font, Border, Fill] Format Green
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for the other codes

with the completed = add that as a Nested IF

=IF(D3="completed", "G", IF(E3 > = TODAY(),"G",IF(F3 > = TODAY(),"A","R")))

Now we should be able to combine the G with an OR

I have not checked formula, are you happy with the formula IF(E3>=TODAY(),"G",IF(F3>=TODAY(),"A","R")) and provides the correct code for the dates only need to add the exception for completed status to be G

IF(OR( D3 = "completed", E3>=TODAY()),"G",IF(F3>=TODAY(),"A","R"))

**Guber1** · 12-09-2021, 05:46 AM

Amazing - spot on. Thank you so much.

**etaf** · 12-09-2021, 06:13 AM

you are welcome

Advice appreciated: Conditional formatting based on dates combined with an IF query

LinkBack

Thread Tools

Rate This Thread

Display

Advice appreciated: Conditional formatting based on dates combined with an IF query

Re: Advice appreciated: Conditional formatting based on dates combined with an IF query

Re: Advice appreciated: Conditional formatting based on dates combined with an IF query

Re: Advice appreciated: Conditional formatting based on dates combined with an IF query

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting based on dates in cells query

[SOLVED] Conditional Formatting based on given dates

Conditional Formatting based on due dates

Conditional Formatting based on dates (2)

Conditional formatting based on dates - don't know how.

IF Statement: Any advice would be appreciated.

Combined dates formatting

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions