Conditional Formatting Help

Closed
Lauren - Jun 1, 2015 at 05:21 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 2, 2015 at 11:41 AM
Hello,

I have a spreadsheet which we use for tracking all outgoing shipments. I have the expected delivery date in one column, and the actual delivery date in another.

I want these to be formatted so that in the event that expected delivery date is yesterday, or a date before yesterday, and the actual delivery date is blank, then that row will be highlighted (e.g red). Ideally, I'd also have it set up so if the expected delivery date is today, and the actual delivery date is blank, I want this to be another colour (e.g. orange)

At the moment I've managed only to highlight the column with expected delivery dates, if this date is today or prior to today.

Thanks in advance for your help!!!

If you need to know, the column with expected delivery dates is I and the column for actual delivery dates is J.

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 2, 2015 at 11:41 AM
Hi Lauren,

Select the number of rows you want to apply conditional format, starting with the 2nd row > conditional format > new rule > use formula:
Formula 1: =AND($I2<>"",$I2<TODAY(),$J2="")
Formula 2: =AND($I2=TODAY(),$J2="")

Best regards,
Trowa
0