Conditional formatting dates

[brainkill3r]

Hi,

How could I use excel conditional formatting for the case where:

-column A I have the dates when an order has been placed
-column B I have the date when the order has arrived
-column C I have the date when they are estimated to arrive.

I would like conditional formatting to mark the row for all 3 dates, where an order has been placed (column A) but the estimated arrival date is missing or the estimated arrival date has passed already.

Any help is appreciated.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[brainkill3r]

Hi,

I have attached a form with before and after and included some comments. Hope it helps.

Please let me know if you need any other info.

[Richard Buttrey]

Hi,

One way as a single CF rule in B5

=OR(AND($B5 < > "",$D5=""),AND($D5 < > "",$C5="",$D5 < TODAY()))

See attached

[brainkill3r]

Hi,

this seems to work fine for just row 5 however for others it does not. Any suggestion how to fix this instead of using a condition for each new row?

edit: i was writing something wrong, the formula works perfectly. Thank you so much!

[Richard Buttrey]

Hi,

this seems to work fine for just row 5 however for others it does not. Any suggestion how to fix this instead of using a condition for each new row?

edit: i was writing something wrong, the formula works perfectly. Thank you so much!

WHy do you say it's only working for row 5.

The workbook I attached has the CF covering 20 rows

[Richard Buttrey]

Thanks for the rep.

[brainkill3r]

Richard,

By any change, when you will want to kill some time, could you please explain to me how exactly does this formula work? Could you maybe break it down and explain each part of it?
Since this post, I have always used it for and it does save a lot of time therefor I am really thankful for your help. However, I'm also a bit greedy and would like to understand exactly how it works.