Conditional Formatting Based on Dates

Ian4212

Board Regular
Joined
Dec 5, 2006
Messages
74
Hi All, I am trying to do conditional formatting based on a few different dates relative to todays date:

Col BCol CCol DCol ECol FCol GCol HCol ICol JCol KCol LCol MCol N
02/02/2021​
16/03/2021​

In Col F I have a keyed date with Col G a formula to show a date 6 weeks later. I want to format columns B to N as follows:

  • No Formatting if no data in any columns
  • If any data entered in any column, then format to fill Orange (B to N)
  • if date in Col G is within 2 weeks of todays date then format to fill B to N in Yellow
  • if date in Col G is same or later than todays date then format to fill B to N in Red
  • Over-ride - if I input a 1 in column O then fill all with Green
Hope this makes sense and thanks in advance.

Ian
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
you will need to setup a few conditional formatting rules - and make sure in correct order and use stop if true

select the range B to N - lets say B2 to N1000

then a formula for your override condition
=$O2 = 1
format Green and make the 1st rule and stop if true

next formula
=$G2 > = today()
format red and make the 2nd rule and stop if true

Next formula
=$G2 > = today()-14
format yellow and make the 3rd rule and stop if true

next formula
a few ways to do this
B to N is 13 columns
So
=COUNTBLANK( $B2:$N2) <> 13
format ORANGE
But as last, will not overide the yellow and red dates or 1 in O
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
ok, if stuck , I can setup a sample sheet and load with XL2BB here
 

Ian4212

Board Regular
Joined
Dec 5, 2006
Messages
74

ADVERTISEMENT

yes please
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
OK, so may not be doing the days quite right
if date in Col G is same or later than todays date then format to fill B to N in Red
Later or Earlier - so today is 5/feb - what date entered should highlight - I always get mixed up with later type words
NOTE I'm in UK, BUT the date format changes to MM/DD format and NOT DD/MM so watch that date

NOT sure from profile what country or version of excel you are using, maybe worth updating the version of excel in use


BUT we can sort that
Also note the 1 in O overides all other colours

anyway - heres the example in XL2BB. - I have also added to dropbox - BUT it will not be on dropbox for more than a few days

Book3
ABCDEFGHIJKLMNO
1Col BCol CCol DCol ECol FCol GCol HCol ICol JCol KCol LCol MCol N
22/7/21
31/26/211
4X
5
62/1/211
71/14/21
81/29/21
9
101/26/21
11
121/23/21
131
14
15
161/17/20
173/15/211
181/14/211
19
20
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:N100Expression=$O2=1textYES
B2:N100Expression=$G2>=TODAY()textYES
B2:N100Expression=$G2>=TODAY()-14textYES
B2:N100Expression=COUNTBLANK( $B2:$N2)<>13textYES


 

Ian4212

Board Regular
Joined
Dec 5, 2006
Messages
74
I am Uk too so the date formats are dd/mm/yyyy. I looked at the colours in you example above and a few look odd? e.g, row 2 is 7 Feb so should not be red until todays date goes past. row 8 is 29 Jan and as we have now passed this date it should be red.

If the date is within 2 weeks of todays date it should be yellow:

1612544471964.png


Hope this makes sense? I really appreciate you spending the time to help
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
yes I suspected i had the dates wrong
So before i change to check

RED anything that is earlier than today - so any date in the past
and YELLOW
any date in the future upto 2 weeks in future

RED
today = 5th feb
anything earlier than 5th FEB - 1st jan , 1st feb etc should be RED
What if you have a 1 in O ? still RED or changes to green
WHAT about ORANGE ? ONLY if no date entered in G or no 1 entered in O

YELLOW
any date in the future upto 2 weeks in future
So today is 5th Feb - anydate between the 5th FEB and the 19th FEB (14days ahead) would be YELLOW
What if you have a 1 in O ? still YELLOW or changes to green
WHAT about ORANGE ? ONLY if no date entered in G or no 1 entered in O
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

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