Formula & Conditional Formatting Issue

[RaiderKY]

Hi All,

Please excuse my ignorance here, but I am trying to setup a formula for column I1 that will calculate if cell H1 is blank, then cell I1 will also remain blank. But If cell H1 contains a value, I1 will run the WORKDAY(G1,H1) and return a value, in this case returning a value of 22/03/2019. I have placed the following formula in I1 =(IF(H1<>"","Not Blank","")) and this returns a 'Not Blank' value, but is there a way to modify this to run the WORKDAY formula?

G H I J

Start Date Allocated Days End Date Completion Date
1 20/03/2019 2

The second part of my problem is regarding conditional formatting, for example once the above is working and column I1 is populated with an 'end date value' based on the WORKDAY formula, I want to manually enter a date value under J1, and for J1 to change color based on the following:

If I1 is blank/ has no value then J1 will also remain blank (same color as my blue/ white table)

If I1 is populated, and the value of J1 is >G1 & <= I1 then cell J1 will turn green

If I1 is populated, and the value of J1 is >I1, cell J1 will turn red

I'm sure it's a real simple fix and something that I am overlooking, but as I'm sure you're aware - I'm not overly savvy with Excel and any assistance to resolve the above would be appreciated.

Many thanks.

[Special-K]

See below...

[Special-K]

First part

IF(AND(G1<>"",H1<>""),WORKDAY(G1,H1),"")

Second part

Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...
2 formulas needed

=AND(I1 < > "",J1 > G1,J1 < = I1)
format as green

=AND(I1 < > "", J1 > I1)
format as red

I think this should work, am just worried about formulas conflicting, red might override green

[RaiderKY]

First part

IF(AND(G1<>"",H1<>""),WORKDAY(G1,H1),"")

Second part

Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...
2 formulas needed

=AND(I1 < > "",J1 > G1,J1 < = I1)
format as green

=AND(I1 < > "", J1 > I1)
format as red

I think this should work, am just worried about formulas conflicting, red might override green

Hi Special-K,

Many thanks for your help, the formula works perfectly. Thank you.

As you suspected, the conditional formatting conflicts where the red overrides the green. Is there anything you can advise to rectify this?

Thanks once again.

[Special-K]

What values do you have in G1 I1 J1 ?

[Special-K]

There's a tick box when entering the formula that says "Stop If True"
This will stop it from executing any other CF formulas.
That should work.

[RaiderKY]

Hi Special-K,

Just a quick update to thank you for your assistance. This issue has now been resolved.