Conditional Format a row based on a date range

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
114
Hi,

I’ll be damned if this doesn’t get me EVERY. SINGLE. TIME. Ugh! OK - enough complaining.

I have a table. There is a start date in column “E” and an end date in column “F.”

I’d like to add conditional formatting to highlight a row based on those dates.
RED - if the end date (column F) is within 10 days (before) Today()
YELLOW - if the end date (column F) is from 30-10 days before Today()

Sometimes the end date is blank. If it’s blank, I’d also like to add a format to that (so I follow up to get the end date).

Thanks in advance!

Judi
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
How about
+Fluff New.xlsm
ABCDEFGHI
1Column EColumn I
201/09/202026/08/2020Expired
301/10/202027/08/2020Expiring
401/02/202128/08/2020Current
5
630/08/2020
731/08/2020
8
906/09/2020
1007/09/2020
11
1209/09/2020
1316/09/2020
1417/09/2020
1518/09/2020
1619/09/2020
1720/09/2020
1821/09/2020
Main
Cell Formulas
RangeFormula
I2:I4I2=IF(E2<TODAY(),"Expired",IF(E2<TODAY()+120,"Expiring","Current"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:I21Expression=AND($F2>=TODAY()-10,$F2<=TODAY())textNO
A2:I21Expression=AND($F2>=TODAY()-20,$F2<TODAY())textNO
A2:I21Expression=$F2=""textNO
 
Upvote 0
This is helpful, but not quite getting me where I need to be. I got one that I needed, but I still need a formula that formats the row if the date in column F is approaching. Maybe within a 20-day window. So, would that be =AND($F2>TODAY()-20,$F2<TODAY())...?? I'm gonna go try that. If you have any other, better ideas, please share!
 
Upvote 0
Do you have the CF rules in the same order as shown in post#2?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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