Conditional formatting blank cell

Billvh

New Member
Joined
Oct 28, 2014
Messages
17
I have a question on conditional formatting that I was hoping I could get some help with.


Amount
Due Date
Paid Date
$2,147.54​
1/21/21​
12/22/20​
$47.00​
1/26/21​

I have three columns.

Column A is the amount due

Column B is the due date

Column C is the date that the amount was paid

I would like to set conditional formatting for column C to do the following:
  • If a cell in column C is empty within 14-days of the due date I would like for it to turn yellow
  • If a cell in column C is empty within 7-days of the due date I would like for it to turn red

Thanks for your time and your help.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
604
Office Version
  1. 2016
Platform
  1. Windows
Book1.xlsm
ABC
1AmountDue DatePaid Date
2$2,147.5412/1/202121/12/2020
3$47.0010/1/2021
Sheet8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2Expression=IF(C2="",B2-TODAY()<8,"")textNO
C2Expression=IF(C2="",B2-TODAY()<15,"")textNO
C3Expression=IF(C3="",B3-TODAY()<8,"")textNO
C3Expression=IF(C3="",B3-TODAY()<15,"")textNO


Note that my date format is dd/mm/yyyy
 
Solution

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
no need to apply it one by one select all "C" range and apply The Two formula and be sure that Row not be Fixed by $ $B$2 $B2


Book1
ABCDE
1ADP
2$ 2.0012/25/202012/26/202014
3$ 5.0012/26/202012/27/202013
4$ 9.0012/27/202012
5$ 4.0012/28/202011
6$ 9.0012/29/202010
7$ 4.0012/30/202012/31/20209
8$ 1.0012/31/20208
9$ 6.001/1/20217
10$ 7.001/2/20216
11$ 58.001/3/20215
12$ 65.001/4/20211/5/20214
13$ 24.001/5/20213
14$ 12.001/6/20212
15$ 548.001/7/20211
Sheet2
Cell Formulas
RangeFormula
C12,C7,C2:C3C2=B2+1
E2:E15E2=NOW()-$B2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C20Expression=AND($C2="",NOW()-$B2>=1,NOW()-$B2<7)textNO
C2:C20Expression=AND($C2="",NOW()-$B2>7,NOW()-$B2<=14)textNO
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
604
Office Version
  1. 2016
Platform
  1. Windows
Yes Dossfm0q is correct. You can select whole range to apply formula and key in the condition only once. I'm old school guy 😄[/USER]
 

Billvh

New Member
Joined
Oct 28, 2014
Messages
17

ADVERTISEMENT

Thanks again for your help, I appreciate it. I’m not doing something right and I can’t pinpoint what the issue is.

Here is what I have done:
  • changed the date format as you suggested to dd/mm/yyyy
  • selected cell C3
  • selected condition formatting
  • selected new rule
  • selected use formula to determine which cells……
  • pasted this formula
  • =IF(C3="",B3-TODAY()<8,"")
  • I made sure that the columns and rows were correct
  • Selected the formatting and clicked on okay
  • Clicked okay again
  • Went back into manage rules
  • Selected stop if true
  • Clicked apply
  • Clicked okay

Nothing happened. Any suggestions?


Thanks,
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
604
Office Version
  1. 2016
Platform
  1. Windows
I did not mean for you to change date format. It is just because of my default format. JUst make sure you date is actually date in the cell, else it will become string and will not calculate anything ;)
 

Billvh

New Member
Joined
Oct 28, 2014
Messages
17

ADVERTISEMENT

The cell is calculated as the date. Here is what I have, do you see anything that may look wrong.

1610141944249.png
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
604
Office Version
  1. 2016
Platform
  1. Windows
You can install this Add-In to select range and copy instead of screen capture. This will help others to easily copy and paste actual spreadsheet


Anyway, at this moment I have no idea what was wrong causing it not to work. Can you try set to IF C=0 instead of "", I was wondering if the the cell has suppressed 0 value or maybe there is space in C. Just wild guess. :biggrin:

We did not use Stop if True. Try unselect it if it makes any difference.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,359
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Why you don't Use AND function at @Dossfm0q Post.
Try this:
Excel Formula:
=AND($C3="",NOW()-$B3>=1,NOW()-$B3<7)

AND

Excel Formula:
=AND($C3="",NOW()-$B3>=8,NOW()-$B3<15)
 

Billvh

New Member
Joined
Oct 28, 2014
Messages
17
I figured it out.

I changed the formatting on the cell to date, but it didn't update until I went back in and change the date itself. After that everything worked fine.

I really appreciate all of the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,342
Messages
5,601,070
Members
414,426
Latest member
fraru

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