Conditional Formatting Dates including Dates Passed

[All_At_Sea]

Hi,

I'm hoping someone could point me in the right direction on Conditional Formatting of Dates?

I've found a solution from searching the forum that suits my needs, although I'd like to add one additional condition. This is where I'm coming unstuck.

This is what I've used: -

Book1.xlsx
	A	B	C	D
1	course	Claire	Sooraj	Paula
2	Autism eLearning	1/2/2024	No Expiry	No Expiry
3	CarePlanningF2Ftraining			11/21/2023
4	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
5	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
6	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D6	Expression	=AND(B2-TODAY()<=30,B2-TODAY()>0)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=90,B2-TODAY()>30)	text	NO
B2:D6	Expression	=AND(B2-TODAY()<=365,B2-TODAY()>90)	text	NO

Book1.xlsx
	A	B	C	D
1	period (days) =	30
2
3	course	Claire	Sooraj	Paula
4	Autism eLearning	1/2/2024	No Expiry	No Expiry
5	CarePlanningF2Ftraining			11/21/2023
6	CareforOlderAdults	1/31/2024	No Expiry	No Expiry
7	Dysphagia training	1/17/2024	1/17/2024	8/6/2024
8	Team building	3/27/2024	3/27/2024	8/17/2023
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:D8	Expression	= IF(AND($B$1>0,$B$1<=30),AND(B4-TODAY()<=30,B4-TODAY()>0))	text	NO
B4:D8	Expression	= IF(AND($B$1>30,$B$1<=90),AND(B4-TODAY()<=90,B4-TODAY()>30))	text	NO
B4:D8	Expression	= IF(AND($B$1>90,$B$1<=365),AND(B4-TODAY()<=365,B4-TODAY()>90))	text	NO

What I'd like to add is if the date is todays or has passed highlight it red.

I've tried adding: -

=AND(D6-TODAY()<=7)

But this causes all blank cells in my range to be coloured and not the one(s) with todays date or passed ones.

I feel I'm going around in circles, so any assistance would be gratefully received.

Thanks in advance.

 

[6StringJazzer]

The formula you showed uses AND but only has one argument. So AND is superfluous.

Your condition checks to see if the date in D6 is earlier than 7 or more days in the future. That is in the past, or in the next 7 days.

A blank cell will be interpreted as 0, which is a date in the past, so that's why blank cells are being filled.

To check for dates today or in the past:

=AND(D6<>"",D6<=TODAY())

If the range for this rule overlaps the ranges for the rules you already have, you will have to determine which ones take precedence and then put them in the appropriate order.

I'm not sure D6 is the correct reference here because your data and other rules start in row 4. It should probably be D4.

 

[All_At_Sea]

Many thanks for the reply @6StringJazzer appreciate it!

Fully understand your explanation and my error.

Using what you provided has worked, with one exception, but that's only after having knocked up the sheet a little further.

WSE Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	25/08/2024
3
4	Item	MOP	Date Due										Notes
5	Liferafts	3
6
7	Seacrewsaver	1
8
9	HDJL MK5	1	1-10	11-25	26-34	35-40	41-50	51-63	63-73	74-75
10			09/10/2024	01/02/2025	23/11/2024	19/09/2024	23/09/2024	08/01/2025	08/01/2025	22/11/2024
11	JON BUOY	1	17/11/2024
12	RELJ	1	17/01/2025	04/09/2024
13	OOSS	5	25/10/2024
14	Jasons Cradle	4	26/07/2025
15	GSLJ MK4	1	05 04 DK	03 02 DK	01 DK	1 DK	EMF	Emergency Boxes	Pilot Cabin
16			15/02/2025	09/02/2025	14/09/2024	27/01/2025	05/01/2025	23/08/2024	13/02/2025
17	COSALT	1	06 05 04 DK	03 02 DK	01 1 DK	MCR EMF	Aircrew Room	Bridge MCR	Emergency Boxes	Hospital	Child
18			19/01/2025	25/10/2024	18/02/2025	07/11/2024	26/10/2024	26/10/2024	03/11/2024	03/11/2024	02/12/2024
19	MRS	3	17/11/2024
Sheet2

Cell Formulas
Range	Formula
A2	A2	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:K19	Expression	=AND(C6<>"",C6<=TODAY())	text	NO
C6:K19	Expression	=AND(C6-TODAY()<=30,C6-TODAY()>0)	text	NO
C6:K19	Expression	=AND(C6-TODAY()<=90,C6-TODAY()>30)	text	NO
C6:K19	Expression	=AND(C6-TODAY()<=365,C6-TODAY()>90)	text	NO

From the above you'll see that where I have entered "1-10" above 09/10/2024 for the HDLJ MK5 it has applied the conditional formatting because it's treating this as a Date, even though I've Formatted the Cell as Text.

What can be done to stop this from happening?

 

[MARK858]

Did you format the cell as Text before putting the 1-10 in the cell?

 

[All_At_Sea]

Did you format the cell as Text before putting the 1-10 in the cell?

No as it happens, it wasn't until I entered the 1-10 and excited the cell I realised it needed to be Text.

It has now been changed, but made no difference, even tried entering '1-10 to try to force it to Text

 

[MARK858]

Actually it comes up as true in the conditional formatting as the text (now you have converted it) is being seen as greater than any number, you'll have to test if it is a number

=AND(ISNUMBER(C6),C6-TODAY()<=90,C6-TODAY()>30)

 

[All_At_Sea]

Actually it comes up as true in the conditional formatting as the text (now you have converted it) is being seen as greater than any number, you'll have to test if it is a number

=AND(ISNUMBER(C6),C6-TODAY()<=90,C6-TODAY()>30)

Many thanks

Works a dream, appreciate your help.

 

[MARK858]

Happy we could help and welcome to the forum (please remember to amend the other conditions in the same way)