conditional formatting

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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?

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

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
the bit for yellow is correct but the red needs to stay from 7 days before despatch date to the despatch date.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=A1<TODAY()+7
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top