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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
Solution
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
 
Upvote 0
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]
 
Upvote 0
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,
 
Upvote 0
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 ;)
 
Upvote 0
The cell is calculated as the date. Here is what I have, do you see anything that may look wrong.

1610141944249.png
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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