Offset conditional formatting

I can conditional format cells with dates but I have a problem offsetting it. This is my scenario; Column A has due dates (all of the same), Column B date completed and Column C to state Complete or Due/Urgent but what I am after is for Column B to have dates entered, and if the dates preceed or equal the due date, Column C will for dates that have preceeded or equalled the due date turn Green and say "Complete" and if later than the due date to go Red and say "Due/Urgent". I hope this is possible as itr will save a lot of typing, thank you for your help.

Try this:

Where

A1 = Due Date
B1 = Date Completed

Please Login or Register  to view this content.

Hi,

Does this help?

Hi,

I have been trying unsuccessfully for the past hour to get it to accept blanks and therefore leave Column C blank also

Just amend the formula in column C to this

=IF(B1="","",IF(B1<=A1,"Complete","Due/Urgent")) and copy down

but as we have nearly 100 training records for 900 colleagues it can get a little time consuming

All you need to do is once you have got your CF the way you want it on C1, is to click C1, click on the Paintbrush icon, then click on the range of the cells or even click on C:C to apply to the whole column

Thank you for your help oldchippy, most appreciated.