Conditional formatting - Warning signs for approaching deadlines

[charlesbaxter]

Hi everyone, to me this seems like a very difficult question but I am sure there must be the funtionality within Excel to be able to do this.


Essentially, I would like to track items that have gone out to the client which have not yet been returned. The items must be returned within 2 months and I have devised the following Excel structure to track it:


Cell A1 - Date sent to client

Cell B1 - Date returned from client

Cell C1 - Complete (A1 and B1 complete)/Awaiting client (Only A1 complete)

Cell D1 - Warning sign - Amber if B1 is not populated 30 days after the date in A1, Red if B1 is not populated 45 days after the date in B1.


I would presume D1 would involve conditional formatting based on the difference between the current date and date sent to client (A1), then >30 = amber, >45 = red.


I would be grateful for any advice on to whether this is possible or not and any tips on how this could be acheieved.


Thanks


Charles

 

[Luke M]

CF rule 1:

=AND(ISBLANK(B1),TODAY()-45>=A1)

Format red


Rule 2:

=AND(ISBLANK(B1),TODAY()-30>=A1)

Format amber


You want to make the higher number used in rule 1, otherwise, the amber format would be applied all the time (as 45 is greater than 30 still).

 

[charlesbaxter]

Thanks, that is exactly what I needed to do. Brilliant

 

[charlesbaxter]

How would this be done in Excel 2007? it appears to be a different set up for creating forumlae. Thanks

 

[charlesbaxter]

Ignore that last question, I have sorted it however, how can I copy the formula so it applies for A2 to D2/A3 to D3 etc. Thanks