Playing with colours and dates.

[tartanspartan1]

All,


Excel relative novice here and looking for some help for a work problem. Hoping to create a sheet which utilises an automatic 'traffic-light' flagging system for bids received and the monitoring of their deadlines.

For instance, cells with dates before todays date would be highlighted red, those cells which are a week before the deadline (including the present day) are marked yellow, while those more than a week until the deadline are green.

Already had moderate success using the formula (Based on data appearing in the E column):

'=E3-E16<=TODAY()'

However, I am looking for more effective suggestions than this. Particularly with regard to the yellow 'one week until deadline' option.


Also, a more basic question: With regards to filtering, is it possible to filter seperate chunks of data, even though they are covered by the same column, with the same heading?

For instance, I may wish to sort my 'Date Due' column E with regard to Air Supply products from cells E3-E16, but sort the dates of Land Supply products in cells E18-E24 on a seperate basis (i.e. Not have them being combined together).

Thanks for your advice (and patience.....).

Cheers.

[JBeaucaire]

You don't post a sample workbook showing it or explaining what values are where, so you'll have to interpret these suggestions. Let's assume E3 is the DUE DATE.

1) The first conditional formatting formula should be the most likely answer....GREEN. CF formula:
=E3 - TODAY() > 7

2) Within a week...YELLOW...CF formula:
=E3 - TODAY() >= 0

3) Past due...RED.....CF formula:
=E3 - TODAY() < 0