Originally Posted by Emvills

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 whether this can be done via conditional formatting or whether I need to be looking at using some VB code.

Thanks for any help,