Conditional Formatting with date not working

Dihusky

New Member
Joined
Mar 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to do away with column B on my spreadsheet by using conditional formatting to highlight column C. Currently I visually check the dates in column B to be prior to today to determine which budgets need to be actioned. I though I had the conditional formula right but nothing happens. Where am I going wrong? Once I get this right I will apply the principal to 5 more columns.

Copy of Dinah 2021.xlsx
ABCD
1BuildingBudget -7 daysBudget Date
216 CAREEL CLOSE6/01/202113-Jan-21
335 BAYSWATER10/02/202117-Feb-21
4COLUMBIA TOWER
5HATARI25/03/202101-Apr-21
6MERON WOODS
7ROBINA LAKES MEDICAL CENTRE
8SANTA FE BY THE BROADWATER16/03/202123-Mar-21
9SIGNATURE ON HIGH
10TUDOR ROSE COURT
11VARSITY PARK COURT23/03/202130-Mar-21
1226 CAREEL CLOSE5/04/202112-Apr-21
1368 STANHILL DRIVE
14BIRDSONG
15CORONATION GARDENS
16DEE-ELL20/04/202127-Apr-21
17
18
19
FYE (3)
Cell Formulas
RangeFormula
B2:B16B2=IF(C2>0,C2-7," ")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:CExpression="$C2-7<TODAY()"textNO
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

You need to remove the quote marks from within the CF formula.
You may also want to add a test to exclude the blank cells so it may end up as

=AND($C2<>"",$C2-7<TODAY())
 
Solution

Dihusky

New Member
Joined
Mar 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

You need to remove the quote marks from within the CF formula.
You may also want to add a test to exclude the blank cells so it may end up as

=AND($C2<>"",$C2-7<TODAY())
Thank you - so simple
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

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
Top