conditional formatting with dates

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
483
Office Version
  1. 2019
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
 
Sort by date Sort by votes
kelvin_9 said:
I was thinking can i use 1 formula in conditional formatting to highlights my request? dates in below range
Click to expand...
You should at least give an example to see what data you have between each range.

Try the following:

You must put a formula for each color:

Libro1
ABCDE
1
2
3
4
5
630/01/2025
701/02/2025
803/02/2025
905/02/2025
10
11
12
13
1401/03/2025
1503/03/2025
1605/03/2025
1707/03/2025
18
19
20
21
2231/03/2025
2302/04/2025
2404/04/2025
2506/04/2025
26
27
Hoja1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:E113Expression=Y($A6>=HOY(),$A6<HOY()+30)textNO
A6:E113Expression=Y($A6>=HOY()+30,$A6<HOY()+60)textNO
A6:E113Expression=Y($A6>=HOY()+60,$A6<HOY()+90)textNO
 
Upvote 0
Thank you very much for your help, DanteAmor.
First, I apologize for any misunderstanding that may have been caused.
Second, this Excel file is used to record consumable expiry dates. The dates are entered manually within the specified range.
Third, the cells will change color automatically to yellow, blue, or red if the date is below 30, 60, or 90 days respectively.
Fourth, I want all cells to remain unchanged if the date has not been entered.
Fifth, i have tried your formula, but it dosent work propertly on me.

Thank you very much for your answering

test-consumables.xlsm
ABCDE
6January 30, 2025
7February 12, 2025
8July 6, 2025
9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:E113Expression=Y($A6>=HOY()+60,$A6<HOY()+90)textNO
A6:E113Expression=Y($A6>=HOY()+30,$A6<HOY()+60)textNO
A6:E113Expression=Y($A6>=HOY(),$A6<HOY()+30)textNO
 
Upvote 0
Hi Kelvin,
kelvin_9 said:
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
Click to expand...
@DanteAmor is correct. Conditional formatting requires 1 condition per Conditional Formatting Rule (CFR).
The only workaround is to use Icon Sets and see if any of those work for you. They have some limitations though.

It looks like you just need to at least change the order from 30 to 90 like in Dante's example.

Are you looking to highlight dates that are Overdue or Due Within? Reason being is that:
Overdue would use Date - n Days, while​
Due Within would use Date + n Days.​

Dante has helped me on several occasions, so you're in good hands...

Best regards,
 

Attachments

  • 1736043565169.png
    1736043565169.png
    23.2 KB · Views: 4
Upvote 0
Whats in the rows you have not specified ?
BUT not sure what to do for your NON Consecutive ranges
Are they just blank ????
You need 3 rules , one for each colour
If they are blank, then that does not matter and you can use the full range
A6:E113

FORMAT RED
=AND(A6<>"", A6>today()+60, A6<=today()+90)

FORMAT BLUE
=AND(A6<>"", A6>today()+30, A6<=today()+60)

FORMAT YELLOW
=AND(A6<>"", A6>today(), A6<=today()+30)

Book1
A
61/30/25
72/12/25
83/16/25
9
10
117/6/25
121/25/25
13
Sheet1
Cell Formulas
RangeFormula
A8A8=TODAY()+70
A12A12=TODAY()+20
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY(),A6<=TODAY()+30)textNO
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY()+30,A6<=TODAY()+60)textYES
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY()+60,A6<=TODAY()+90)textNO


Perhaps a sample would help here , so we can see if there is something that can be used to enable the full range but exclude the sections you do not want highlighted


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
Hi @etaf

I'd recommend the built in Between Dates option. It's what I'm using currently.
I wasn't sure if OP is looking for Overdue or Due Within. Here's what I've got in my sample workbook.
Below is the VBA code I use to apply the CFR's to ensure they're applied in the correct order, etc.
VBA Testing.xlsm
QRSTU
1Due DateOverdueDue DateDue Within
22024-01-013692025-01-013
32024-01-153552025-01-1511
42024-02-193202025-02-1946
52024-03-312792025-02-2754
62024-05-272222025-03-3186
72024-07-041842025-04-0187
82024-09-021242025-09-02241
92024-10-14822025-09-29268
102024-11-11542025-10-14283
112024-11-27382025-11-11311
122024-12-24112025-11-27327
132024-09-29972025-12-24354
14  
15
16Days OverdueDue Within
1730 days30 days
1860 days60 days
1990 days90 days
CF Dates
Cell Formulas
RangeFormula
R2:R14R2=IF(ISBLANK([@[Due Date]]),"",DAYS(TODAY(),[@[Due Date]]))
U2:U14U2=IF(ISBLANK([@[Due Date]]),"",ABS(DAYS(TODAY(),[@[Due Date]])))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q2:Q14Cell Valuebetween TODAY() and TODAY()-30textNO
Q2:Q14Cell Valuebetween TODAY() and TODAY()-60textNO
Q2:Q14Cell Valuebetween TODAY() and TODAY()-90textNO
T2:T14Cell Valuebetween TODAY() and TODAY()+30textNO
T2:T14Cell Valuebetween TODAY() and TODAY()+60textNO
T2:T14Cell Valuebetween TODAY() and TODAY()+90textNO

It's working with the current sample data where I didn't need to include the Blank cells (no format) or Stop if True for any of them...
Because CFR's are a pain to do manually, I store them as a whole in their relevant Sheet Code so I can apply them all at once. I add the Delete at the top to ensure they are all deleted from their respective range only, then all reapplied.

Hopefully others find it useful...

Highlight Overdue Dates:
VBA Code: 
'Highlight Overdue Dates
Private Sub CFR_Overdue()
'Note: 'StopIfTrue' default is TRUE https://learn.microsoft.com/en-us/office/vba/api/excel.formatcondition.stopiftrue
  
  Dim Overdue As Range: Set Overdue = Range("t_Overdue[Due Date]")
  
  With Overdue

    'Delete all CFR's
    .FormatConditions.Delete
  
    'Cell Value is Blank
'    .FormatConditions.Add Type:=xlBlanksCondition
'    .FormatConditions(.FormatConditions.Count).SetFirstPriority
'    .FormatConditions(.FormatConditions.Count).StopIfTrue = True
    
    'Cell Value = TODAY() and TODAY()-30
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()-30"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 13431551  'Gold Accent 4 80%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    
    'Cell Value between TODAY and TODAY()-60
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()-60"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 16247773  'Blue Accent 5 80%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    
    'Cell Value between TODAY and TODAY()-90
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()-90"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 13551615 'Rose 60%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
  
  End With

End Sub

Highlight Due Within Dates:
VBA Code: 
'Highlight Due Within Dates
Private Sub CFR_DueWithin()
'Note: 'StopIfTrue' default is TRUE https://learn.microsoft.com/en-us/office/vba/api/excel.formatcondition.stopiftrue
  
  Dim DueWithin As Range: Set DueWithin = Range("t_DueWithin[Due Date]")
  
  With DueWithin

    'Delete all CFR's
    .FormatConditions.Delete
  
    'Cell Value is Blank
'    .FormatConditions.Add Type:=xlBlanksCondition
'    .FormatConditions(.FormatConditions.Count).SetFirstPriority
'    .FormatConditions(.FormatConditions.Count).StopIfTrue = True
    
    'Cell Value = TODAY() and TODAY()+30
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 13551615 'Rose 60%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    
    'Cell Value between TODAY and TODAY()+60
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()+60"
    .FormatConditions(.FormatConditions.Count).Interior.Color = 16247773  'Blue Accent 5 80%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False

    'Cell Value between TODAY and TODAY()+90
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=TODAY()", Formula2:="=TODAY()+90"
    .FormatConditions(.FormatConditions.Count).Interior.Color =  13431551  'Gold Accent 4 80%
    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
  
  End With

End Sub
 
Upvote 0
kelvin_9 said:
Thank you very much for your help, DanteAmor.
...
Fifth, i have tried your formula, but it dosent work propertly on me.
Click to expand...

🤦‍♂️

I forgot to translate the formulas, try this:


=AND($A6>=TODAY(),$A6<TODAY()+30)
=AND($A6>=TODAY()+30,$A6<TODAY()+60)
=AND($A6>=TODAY()+60,$A6<TODAY()+90)
 
Upvote 0
DanteAmor said:
🤦‍♂️

I forgot to translate the formulas, try this:


=AND($A6>=TODAY(),$A6<TODAY()+30)
=AND($A6>=TODAY()+30,$A6<TODAY()+60)
=AND($A6>=TODAY()+60,$A6<TODAY()+90)
Click to expand...
thank you very much for your answering, DanteAmor

i've tried your formula in a6:e9, all correct unless blank cell get changed even blank, can this be non-colored?
test-consumables.xlsm
ABCDE
6January 6, 2025
7February 5, 2025
8March 8, 2025
9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:E9Expression=AND($A6>=TODAY()+60,$A6<TODAY()+90)textNO
A6:E9Expression=AND($A6>=TODAY()+30,$A6<TODAY()+60)textNO
A6:E9Expression=AND($A6>=TODAY(),$A6<TODAY()+30)textNO


on the other contray, is it simply change the "days" if i need like below?
however, feb 5 & mar 8 was wrong
test-consumables.xlsm
ABCDE
14January 6, 2025
15February 5, 2025
16March 8, 2025
17
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A14:E17Expression=AND($A14>=TODAY()+30,$A14<TODAY()+60)textNO
A14:E17Expression=AND($A14>=TODAY()+14,$A14<TODAY()+30)textNO
A14:E17Expression=AND($A14>=TODAY(),$A14<TODAY()+14)textNO


thank you very much for your help
 
Upvote 0
etaf said:
Whats in the rows you have not specified ?
BUT not sure what to do for your NON Consecutive ranges
Are they just blank ????
You need 3 rules , one for each colour
If they are blank, then that does not matter and you can use the full range
A6:E113

FORMAT RED
=AND(A6<>"", A6>today()+60, A6<=today()+90)

FORMAT BLUE
=AND(A6<>"", A6>today()+30, A6<=today()+60)

FORMAT YELLOW
=AND(A6<>"", A6>today(), A6<=today()+30)

Book1
A
61/30/25
72/12/25
83/16/25
9
10
117/6/25
121/25/25
13
Sheet1
Cell Formulas
RangeFormula
A8A8=TODAY()+70
A12A12=TODAY()+20
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY(),A6<=TODAY()+30)textNO
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY()+30,A6<=TODAY()+60)textYES
A6:A8,A11:A19Expression=AND(A6<>"", A6>TODAY()+60,A6<=TODAY()+90)textNO


Perhaps a sample would help here , so we can see if there is something that can be used to enable the full range but exclude the sections you do not want highlighted


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
thank you very much for you answering, etaf

i do have xl2bb, but not at office
i've tried your formula, it works property on me in a6:e9
test-consumables.xlsm
ABCDE
6January 7, 2025
7February 6, 2025
8March 9, 2025
9July 7, 2025
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:E9Expression=AND(A6<>"", A6>TODAY()+60,A6<=TODAY()+90)textNO
A6:E9Expression=AND(A6<>"", A6>TODAY()+30,A6<=TODAY()+60)textYES
A6:E9Expression=AND(A6<>"", A6>TODAY(),A6<=TODAY()+30)textNO


however it's not working well in a14:e17 if i changed the "days" to less than 14, less than 30 and less than 60
test-consumables.xlsm
ABCDE
14January 7, 2025
15February 6, 2025
16March 1, 2025
17March 9, 2025
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A14:E17Expression=AND(A14<>"", A14>TODAY()+30,A14<=TODAY()+60)textNO
A14:E17Expression=AND(A14<>"", A14>TODAY()+14,A14<=TODAY()+30)textYES
A14:E17Expression=AND(A14<>"", A14>TODAY(),A14<=TODAY()+14)textNO


thank you very much for your help
 
Upvote 0
Ok, you have a few different solutions now , provided by various members, so I may dip out after this post - so I dont confuse

however it's not working well in a14:e17 if i changed the "days" to less than 14, less than 30 and less than 60
Click to expand...
Not sure i follow now

Whats in the rows you have not specified ?

Are they just blank ????
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
272
Thlama
T
E
  • Solved
Can someone tell me why the first column is not displaying all info?
Replies
3
Views
184
RoryA
RoryA
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
370
Alaa mg
A
B
  • Solved
Unsure if Calculation is Correct
Replies
3
Views
452
Bazza06
B
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,226,848
Messages
6,193,315
Members
453,790
Latest member
yassinosnoo1

Share this page

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
Back
Top