Highlighting overdue tasks

[Guest]

Can anyone tell me how I can go about setting up a formula in excel 2003 for
the following:

In my action plan spreadsheet 2 of the columns are a due date column & a
status column. I am trying to get cells in the status column (which has a
drop down list of items to select from) to automatically change to OVERDUE
when the due date is reached. These 2 columns are next to each other. Can
this be done?

Also, I would love once a cell in the status column reads OVERDUE for that
row to be highlighted in red. Is that asking too much???

Any help would be greatly appreciated!

Thanks
Kate

 

[Guest]

Suggest you use an adjacent col to check for "Overdue".

Assuming "Due dates" running in A2 down (these are assumed real dates), and
B2 down contains the DV selections for the projects' Status: Not started,
Pending, Completed

Put in C2:
=IF(AND(A2<>"",TODAY()>A2,B2<>"Completed"),"Overdue","")
Copy C2 down to cover the max expected extent of data in cols A and B

Then apply the CF like this ..

Select the entire sheet (ensure A1 is active)

Click Format > Cond Formatting
Under Condition 1, make it as:
Formula is: =$C1="Overdue"
Format: Red fill/ white font, bolded, OK out

 

[Guest]

Hello Max

Thank-you for your reply I tried it out & it worked fine!!! However I have
run into another problem.

In the status column you can select from the following using the drop down
list for each cell consisting of Not Started, Pending, Completed & Overdue.

I have set up conditional formatting thanks to some help so that when a
status cell reads Completed for example the row turns green (Condition 1),
for Pending yellow (Condition 2).

However the problem I have now is that even though the date may be overdue &
the C2 column (which is my N2 column) is reading Overdue if the status column
(H column) reads Completed it will stay green or Pending it will stay yellow.

Can I get it so that Overdue will appear in the status column rather than
the C2 column AND so that the due date condition overrides any other
conditional formatting???

Cheers
Kate

 

[Max]

Kate,

To avoid confusion, I'd suggest you remove "Overdue" as a selectable from
the DVs col. Leave this "Overdue" flagging to Excel to auto-monitor in the
adjacent col using the formula as per set-up in the earlier response.

Anyway, with the above said, I do trust that you have sorted out things
since, going by your latest reply to MarkN's response in your other thread
on"Conditional Formatting of rows"? Let me know ..