Setting traffic lights based on todays date against target dates

[HDV]

Hi

I have a spreadsheet I use for calculating target dates for issue of
student results.

At the top I have a cell containing todays date I1

One column contains the first date of the result issue process ie cell
number D6

Next column contains the target date for result issue - cell E6 which
is calculated by formula (20 workdays after the date in D6)

I need to set up a traffic light system in conditional formatting which
highlights the target date in E6, firstly amber when today(I1) is 9
workdays after the date in D6, and then a second condition turning to
red when today (I1) is 12 workdays after the date in D6.

Help - I am utterly confused!

Thanks

 

[Jon Quixley]

You could try something like this:

in Cell D6 enter the following

If(I1<D6+10,1,0)

Then set the Conditional Formatting to first stage D6 =1, format Amber,
second stage D6 =0, format Red.

Cheers
JQ

 

[y_not]

NOt sure if it will hepl but I recently came across "networkdays" which
counts the number of WORKDAYS (not weekends) between two dates.

Sorry if I'm wasting your time

Good luck

Tony