Conditional Formatting Help Needed!

[Lauren7]

Hi,

I'm hoping someone can help!! I'm trying to modernise the way some things are done in my office.. one of those being the tracking of shipments we send out using an excel spreadsheet.

So far this is working great, however I'm struggling with conditional formatting.

I have a column (J) which is for "expected delivery date". In this column I write the date we expect the shipment to arrive.
I have a column (K) which is for "actual delivery date". In this column I enter the date when we receive confirmation of delivery.

I would like to set up conditional formatting to do the following:

Highlight in red when a date in column J is either today's date, or a date in the past but ONLY if column K is blank. Similar to this:

Package	To	Expected Delivery Date	Actual Delivery Date
1	a	03-Jun-2015	03-Jun-2015
2	a	04-Jun-2015
3	a	02-Jun-2015
4	a	02-Jun-2015	02-Jun-2015

<tbody>
</tbody>

Is this possible? Ideally I'd like to format the entire row for the package which is late. If this is not possible then simply highlight the cell where the expected delivery date is would be sufficient!

Thank you for your help!!!!

 

[Lauren7]

https://onedrive.live.com/redir?resid=DBCFCD1E04710A18!154&authkey=!AAztCLz8fsbxXew&ithint=file,xlsx

I've removed some of the data for confidentiality reasons, however I think you can still see what you need?

 

[RoryA]

Your formula is off by one row - you actually have:
=AND($K3="",$J3<=TODAY())
but it should be:
=AND($K2="",$J2<=TODAY())

 

[Lauren7]

Just tried this... now the contents of the spreadsheet are formatted, including the titles however the last row of data is still unformatted.

Sorry to be a pain!! Am I being stupid?