conditional formatting with dates

[kelvin_9]

Greeting to All,

I was thinking can i use 1 formula in conditional formatting to highlights my request? dates in below range
less than 30days = yellow
less than 60days = blue
less than 90days = red

a6:e9
a14:e17
a22:e25
a30:e33
a38:e41
a46:e49
a54:e57
a62:e65
a70:e73
a78:e81
a86:e89
a94:e97
a102:e105
a110:e113

thank you very much for your answering

 

[kelvin_9]

Ok, you have a few different solutions now , provided by various members, so I may dip out after this post - so I dont confuse


Not sure i follow now

here is my sample, hope this help

Dropbox

www.dropbox.com

 

[etaf]

i have downloaded and posted in XL2BB for other members

BUT i dont understand the colours used for the 3 dates shown
Nor do i understand Why its now 14 days
the orginal question said

less than 30days = yellow
less than 60days = blue
less than 90days = red

And as some members has asked - Is this overdue or in future

I would need a lot more explanation to help further - with some example dates for ALL colours and why

test.xlsm
	A	B	C	D	E
1			less than 14 days	less than 30 days	less than 60
2
3	code	code	code	code	code
4	description	description	description	description	description
5	consumable	consumable	consumable	consumable	consumable
6	January 10, 2025		January 3, 2025		July 7, 2025
7		February 1, 2025
8
9
10
11	code	code	code	code	code
12	description	description	description	description	description
13	consumable	consumable	consumable	consumable	consumable
14
15
16
17
18
19	code	code	code	code	code
20	description	description	description	description	description
21	consumable	consumable	consumable	consumable	consumable
22
23
24
25
Sheet1

 

[kelvin_9]

i have downloaded and posted in XL2BB for other members

BUT i dont understand the colours used for the 3 dates shown
Nor do i understand Why its now 14 days
the orginal question said

And as some members has asked - Is this overdue or in future

I would need a lot more explanation to help further - with some example dates for ALL colours and why

test.xlsm
	A	B	C	D	E
1			less than 14 days	less than 30 days	less than 60
2
3	code	code	code	code	code
4	description	description	description	description	description
5	consumable	consumable	consumable	consumable	consumable
6	January 10, 2025		January 3, 2025		July 7, 2025
7		February 1, 2025
8
9
10
11	code	code	code	code	code
12	description	description	description	description	description
13	consumable	consumable	consumable	consumable	consumable
14
15
16
17
18
19	code	code	code	code	code
20	description	description	description	description	description
21	consumable	consumable	consumable	consumable	consumable
22
23
24
25
Sheet1

thank you very much for your answering, etaf

you know what, no matter 30, 60, 90 days or 14, 30, 60 days, just a try i gave a shot
however, i found like what i said, it's not good if i change to another days instead of 30, 60, 90 in original
i just curious and i thought it's the same if i change any "days" of it

btw this is used to calculate a consumable expiry date running expired which i need to be aware and take necessary action
so i would like to use conditional formatting to label a coming expired cosumable it easily

i'm really sorry if i caused any misunderstanding to you all again and again

test-consumables.xlsm
	A	B	C	D	E
6	January 8, 2025	January 8, 2025	March 9, 2025
7	February 7, 2025	February 7, 2025	January 8, 2025	March 9, 2025
8	March 9, 2025		February 7, 2025	January 8, 2025	March 9, 2025
9				February 7, 2025	January 8, 2025
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:E9	Expression	=AND(A6<>"", A6>TODAY()+60,A6<=TODAY()+90)	text	NO
A6:E9	Expression	=AND(A6<>"", A6>TODAY()+30,A6<=TODAY()+60)	text	YES
A6:E9	Expression	=AND(A6<>"", A6>TODAY(),A6<=TODAY()+30)	text	NO

 

[etaf]

OK,
so the conditional formatting has been setup for the future dates
61-90 days
31-60 days
1-30 days

Does that now work OK for you
if not,
whats the issue/error

 

[zero269]

you know what, no matter 30, 60, 90 days or 14, 30, 60 days, just a try i gave a shot
however, i found like what i said, it's not good if i change to another days instead of 30, 60, 90 in original
i just curious and i thought it's the same if i change any "days" of it

Hi Kelvin,

When you change the number of days you want the CFR's to Conditionally Format dates (number values), you just need to keep in mind that's it's most often going to be either of the following:

• TODAY + DAYS
• TODAY - DAYS

From there you'll want to place them in the Order of priority in the Conditional Formatting Rules Manager (CFRM).
You usually want them in Low to High order. Like your 30, 60, 90 days.
When you're confident it works for a single column, you can then expand your Applies To range in the CFRM to something like A6:E110. I think that's what your ranges added up to.

Best regards,