Conditional Formatting Based on Dates

[Ian4212]

Hi All, I am trying to do conditional formatting based on a few different dates relative to todays date:

Col B

Col C

Col D

Col E

Col F

Col G

Col H

Col I

Col J

Col K

Col L

Col M

Col 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

 

[etaf]

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

 

[Ian4212]

thanks I will give it a try

 

[etaf]

ok, if stuck , I can setup a sample sheet and load with XL2BB here

 

[Ian4212]

yes please

 

[etaf]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I	Col J	Col K	Col L	Col M	Col N
2							2/7/21
3							1/26/21								1
4				X
5
6							2/1/21								1
7							1/14/21
8							1/29/21
9
10							1/26/21
11
12							1/23/21
13															1
14
15
16							1/17/20
17							3/15/21								1
18							1/14/21								1
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:N100	Expression	=$O2=1	text	YES
B2:N100	Expression	=$G2>=TODAY()	text	YES
B2:N100	Expression	=$G2>=TODAY()-14	text	YES
B2:N100	Expression	=COUNTBLANK( $B2:$N2)<>13	text	YES

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Ian4212]

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:

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

 

[etaf]

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