Excel - Conditional formatting based on due dates

[Stroppy]

hi Excel people, hoping this is the right forum and please redirect me if it's not. I work in a training organisation and admin are trying to track student work...date incoming, date due to be marked (=+21 days), and date marked, (and which trainer has the work). We're thinking a simple solution is this: Date, Student, Title of Work, Due date to be marked (= date received + 21), Status (which is simply a highlighted cell in green, yellow, orange, red).
Green = > Date received and < Date received + 7
yellow = > Date received + 7 and < date +14
Orange = > Date received + 14 but < date received +21
Red = > Date received + 21

May be that only 3 colours....green, yellow, red.

ie Green if < Date Received + 14
Yellow if between 14 nd 21 days since handed in
Red if not marked by 21 days.

Hmmmm....Because when the work is marked, need to record that. It would be useful for quality reporting to know how long it took from receipt to marking.

The other thing is that there will be a trainer (in tray) name and list will be sortable on name. And other fields.

If anyone has any ideas would appreciate it. Will try to hone the question today.

 

[SkipVought]

hi,

What's your question?

Have you tried the Conditional Formatting feature? You need a CF for each condition.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Stroppy]

Sorry...not well put. So, question is if column D, for eg, is the 'status' column that, due to conditional formatting, highlights cells in, say, green yellow or orange...based on time lapsed since work was handed in...how do I do that. Have done some conditional formatting about 12 years ago...not fluent in it now.

 

[SkipVought]

I assume that you are using Excel 2007+

It will help as you add rows to your table (I also assume that you have a proper table), if you were to make your table a Structured Table, as the CF range will automatically change to correspond the the ST row size.

Select the entire column(s) of data you want CFed. Shade the selection GREEN.

All formulas will be with respect to the TOP LEFT CELL of the selection, which I assume will be D2. I assume that Date Received is column A.

[pre]
First CF RED: =Today()-A2>=21
Next CF YELLOW: =Today()-A2>=15
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]