Conditional Format a row based on a date range

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
108
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
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
 

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
108
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Do you have the CF rules in the same order as shown in post#2?
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,304
Members
410,545
Latest member
Upsindustrial20
Top