Conditional Formatting

downunder400

New Member
Joined
Aug 24, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm trying to activate conditional formatting to an excel sheet so when a date hits 60 days before expiry highlight it yellow, when it hits 30 days highlight it orange and when it expires highlight it red.

Is this possible and if so how?!

Thanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi & welcome to MrExcel
Can you post some sample data using the XL2BB add-in.
 
Upvote 0
dateSiteNameCompany NameInduction NoInduction Expiry
12/01/2018Altens
1​
12/01/2020​
06/02/2018Altens
2​
06/02/2020​
06/02/2018Altens
3​
06/02/2020​
06/02/2018Altens
4​
06/02/2020​
06/03/2018Altens
5​
05/03/2020​
 
Upvote 0
So in Column F, there are numerous dates from over 6 months ago to 6 months away yet. How do I highlight them so when a date hits 60 days away from the day the sheet was opened it turns them yellow, then 30 days away turns them orange, then when the date is the day the sheet is opened or past they are red
 
Upvote 0
Unless I'm missing something all those dates would be highlighted in red.
 
Upvote 0
For the selection I have sent yes they would all be red but the full sheet has dates in the future too as late as November this year
 
Upvote 0
In future it would help if you supplied a realistic sample showing all the various possibilities. ;)
How about
+Fluff New.xlsm
ABCDEF
1dateSiteNameCompany NameInduction NoInduction Expiry
212/01/2018Altens123/08/2020
306/02/2018Altens225/08/2020
406/02/2018Altens323/09/2020
506/02/2018Altens424/09/2020
606/03/2018Altens525/12/2020
7
8
9
Data
 
Upvote 0
Oops, forgot to include the CF rules
+Fluff New.xlsm
ABCDEF
1dateSiteNameCompany NameInduction NoInduction Expiry
212/01/2018Altens123/08/2020
306/02/2018Altens225/08/2020
406/02/2018Altens323/09/2020
506/02/2018Altens424/09/2020
606/03/2018Altens525/12/2020
7
8
9
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F9Expression=AND(F2<>"",F2<=TODAY())textNO
F2:F9Expression=AND(F2<>"",F2<=TODAY()+30)textNO
F2:F9Expression=AND(F2<>"",F2<=TODAY()+60)textNO
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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