Conditional Formatting for Dates for multiple scenarios

Attached is a sample spreadsheet in which I need to apply various Conditional Formatting rules in column E “Date Received."

If there IS a date in the Initial Review Complete (G2), then the following calculation applies:
Initial Review Complete (G2) – Date Received (E2) = Number of Days; if
Number of Days >5 and <11 highlight cell in green
Number of Days >11 highlight in orange

If there is NOT a date in the “Initial Review Complete” (G2) cell then the following calculation applies:
Today’s Date – Date Received (E2) = Number of Days; if
Number of Days >5 and <11 highlight cell in yellow
Number of Days >11 highlight in red

All of the date calculations must consider weekend days and Holidays (see worksheet titled “HOLIDAYS”)

Can I use the value created by a formula in column H, since that column is a formula and not a value?

If there is a “N/A,” “Hold,” or a blank cell in column E, then no conditional formatting should apply.

Here is clarification - I should have used words rather than symbols and added the "equal to" for clarity - sorry!


If there IS a date in the Initial Review Complete (G2), then the following calculation applies:
Initial Review Complete (G2) – Date Received (E2) = Number of Days; if
Number of Days greater than or equal to 5 and less than or equal to 11 highlight cell in green
Number of Days greater than or equal to 12 highlight in orange

If there is NOT a date in the “Initial Review Complete” (G2) cell then the following calculation applies:
Today’s Date – Date Received (E2) = Number of Days; if
Number of Days greater than or equal to 5 and less than or equal to 11 highlight cell in yellow
Number of Days greater than or equal to 12 highlight in red

That said, I am honestly looking to possibly tweak the number of days (5, 11, etc.), but wanted to get the formulas and logic.

Have you tried the suggestion in post #2?

Simply change >5 to >=5 to account for the change that you made in post #4.

I have made some tweaks below to your original set based on the suggestion to add the "="

When calculating the dates I need to factor into the subtraction for when there are dates in both columns E and G the weekends and the Holidays (see HOLIDAYS worksheet). I am not sure if this is the case.

Also, when there is no date in column G, the I need to subtract the date in column E from the date "today."


=AND(G1<>"",H1>=5,H1<=11)
Fill Green

=AND(G1<>"",H1>=12)
Fill Orange

=AND(G1="",H1>=5,H1<=11)
Fill Yellow

=AND(G1="",H1>=12)
Fill Red

Originally Posted by Hedy

When calculating the dates I need to factor into the subtraction for when there are dates in both columns E and G the weekends and the Holidays.

That is what the formula in column H is doing. We can use the results of that formula in the Conditional Formatting.

Also, when there is no date in column G, the I need to subtract the date in column E from the date "today."

And put that number where, in column H?
Are you saying that the formula in column H is not producing the desired results when there is no date in column G and there is a date in column E?

There are no instances of this in the sample that you shared and you have not mentioned this criteria in any of your previous posts.

Ok - thank you - yes, since we are using column H for the number of days, application of the weekdays and holidays works perfectly. We are applying conditional formatting only to column E.

Also, the two formulas Yellow or Red are for when there is NO date in column G and the date in column E is subtracted from "Today." You are correct that I did not have a row or two with this example - it was only part of the verbiage and I apologize.

I am attaching a new version of the spreadsheet (Sample v01) with two rows without dates in Cells G2 and G4 for this purpose together with some notes in column N.
*Row 2 (cell E2) which should be yellow since the date is 5/23 and within the 5 to 11 days
*Row 4 (cell E4) is correctly highlighted in red since 3/26/18 is greater than 12 days from today

Both the red and yellow conditional formatting should be based on "today's date" since this highlighting will only apply when there is no date in Column G. Right now, the conditional formatting formula is for the Yellow and Red is looking at column H, but it should only reference column H if there IS a date in column G. If there is not a date in column G, then instead of looking at column H, the formula should look at today's date. And again, weekdays and holidays must be factored into this.

Also, there should not be any conditional formatting in cells that are not yet populated. I think that some of the problem is that I have dragged down the formulas in other columns, so as people enter rows of data, some of the formulas will automatically do the calculations, but the conditional formatting is recognizing some of this as data.

I realize that this is confusing, and I have had a time trying to understand the criteria from my colleague/ "customer." I hope this response helps to clarify.