Compare the difference between 3 dates, 1 of which is optional

[meaningofexcel]

Hi,

I have a conditional formatting formula that "paints" the cell of a gantt chart start and end dates. I'm introducing Travel date to the equation but this date is optional, sometimes it's empty, sometime it's not.

How can I build this formula to look at the travel date as well if it exists?
Column H is Travel Date; Column I is Start Date, Column J is End Date.

For comparing 2 dates my formula was:
=and(AC$2>=$I3,AC$2<=$J3)


I tried this for 3 dates but it doesn't work:
=AND(AC$2>=$H3, OR(AC$2>=$I3),AC$2<=$J3)


Thank you.

 

[Fluff]

Maybe
=AND($AC$2>=$H3,$H3<>"", OR($AC$2>=$I3),$AC$2<=$J3)

 

[meaningofexcel]

Maybe
=AND($AC$2>=$H3,$H3<>"", OR($AC$2>=$I3),$AC$2<=$J3)

Hm.

First, since this is a conditional formatting function, the rows are not absolute, so I tried the formula you have mentioned without $:

=AND(AC$2>=$H3,$H3<>"", OR(AC$2>=$I3),AC$2<=$J3)

This basically colors only the cells(rows) that have a travel date now. Everything else is blank.
It looks like you just added a "if H3 is not empty" without specifying - "if H3 is not empty take value from H3 instead of I3, if It's empty, take value from I3.

 

[meaningofexcel]

Ha,

Looks like typing what I'm trying to achieve actually helped me think of the correct formula:


=IF($H3<>"", and(AC$2>=$H3,AC$2<=$J3), and(AC$2>=$I3,AC$2<=$J3))



Thank you!

 

[Fluff]

It looks like you just added a "if H3 is not empty"

Correct

without specifying - "if H3 is not empty take value from H3 instead of I3, if It's empty, take value from I3.

You didn't specify that's what you wanted


Glad you got it sorted & thanks for the feedback

 

[meaningofexcel]

You didn't specify that's what you wanted

Actually I did but I realize that I did not explain my situation clearly.

"How can I build this formula to look at the travel date as well if it exists?"

Thanks for replying!

Take care

 

[Fluff]

I thought you had that sorted.
If not can you please post some data showing what you have & how you would like it to look.