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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,052
Office Version
  1. 365
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
54,268
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
8,052
Office Version
  1. 365
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,223
Members
430,116
Latest member
d1pakjha

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