conditional formatting

[singingsister]

i have a large order spreadsheet which tracks despatch dates of orders so I chase deliveries BEFORE they are sent late :wink: as in, if the despatch date is 2 weeks away, the date will flag yellow, when it's 1 week it changes to red.

I now want to amend it slightly so that for the yellow flag, it only flags yellow for one day when it gets to 2 weeks before despatch. As in, if the despatch date is the 27th of August (2 weeks from today), it will only show yellow today as tomorrow's date is under two weeks away. I still want the red flagging system I have to carry on.

Can anyone help me?

 

[Oaktree]

It sounds like you want yellow only if its dispatch date is *exactly* two weeks from today and red if it's less than two weeks from today... is that right?

yellow's criterion: =A1=TODAY()+14
red's criterion: =A1<TODAY()+14

 

[singingsister]

the bit for yellow is correct but the red needs to stay from 7 days before despatch date to the despatch date.

 

[Peter_SSs]

the bit for yellow is correct but the red needs to stay from 7 days before despatch date to the despatch date.

Try just changing the red condition to:

=A1<TODAY()+7

 

[Oaktree]

What happens if it's 7-14 days before the dispatch? If you keep yellow's criterion and use Peter's red criterion, items 7-14 days before the dispatch will not have any conditional formatting... is that what you want?

 

[singingsister]

14 to 7 days there should be NO colour.