How to conditional format cells based on whether today's date is past a due date criteria?

Hi,

Wondering if someone can help point me in the right direction.

I've got one cell (A) where a date and time will be input to acknowledge reciept of a report.

The next cell down (B) i'm trying to conditional format based on whether an email reciept of the report has been sent. The email has to be sent within 1 day of the report being recieved by my user.

I want cell B to change colour based on the following:

-If cell A isn't blank (so a date has been input) Cell B should be colour coded based on its status:

-Cell B should automatically turn red when it's a full day after the date input into cell A
-Cell B should automatically turn yellow if today's date and time is in between the date/time in cell A and the date/time in cell A +1
-Cell B should automatically turn green when the task has been completed (which is signified by the completion date being entered in cell B)

I've managed to get something similar working by getting the cell B to change colour based on the user inputting a date into cell B, and comparing the date input into B with A. But I want the colour to change automatically to flag when the emailing task of cell B is overdue and needs completing based on the actual date/time it is now.

I don't know wther this can be done via conditional formatting or whether I need to be looking at using some VB code.

If anyone could point me in the right direction I would be really grateful.

Try these:

-Cell B should automatically turn green when the task has been completed (which is signified by the completion date being entered in cell B)

=AND(A1<>"",B1<>"")

-Cell B should automatically turn red when it's a full day after the date input into cell A

=NOW()>=A1+1

-Cell B should automatically turn yellow if today's date and time is in between the date/time in cell A and the date/time in cell A +1

What do you mean by "Plus 1"?

If you mean a Day, we already did that above, if you mean 1 Hour, then this should work:

=NOW()>=A1+TIME(1,,)

Also, the sequence is important.

In the "Conditional Formatting Rules Manager" window they should appear as:

Green Top of list: =AND(A1<>"",B1<>"")

Red Middle of list: =NOW()>=A1+1

Yellow Bottom of list: =NOW()>=A1+TIME(1,,)