|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do i highlight a cell red if no date has been entered 30 days after a date in another cell??
Example: Cell A2 contains the date 6/1/22. I want Cell B2 to be highlighted red if nothing is entered in to cell B2 after 30 days. How do I do it?
|
#2
|
||||
|
||||
This could be your formula for cell B" with Conditional Formatting with red fill:
=IF(AND(B2=""),TODAY()-A2>30) |
#3
|
|||
|
|||
That worked! Thanks a lot I really appreciate it!
|
#4
|
|||
|
|||
To take it one step further: Cells A2:A6 contain dates. Cells C2:C6 have letter grades (A, B, or C) I want Cells B2:B6 to be highlighted red if nothing is entered in to those cells after 30 days if they're an A, or 15 days if they're a B, or 7 days if they're a C. How do I do that?
|
#5
|
||||
|
||||
See if I got it right:
=OR(AND(C2="A",TODAY()-A2>30),AND(C2="B",TODAY()-A2>15),AND(C2="C",TODAY()-A2>7)) |
#6
|
|||
|
|||
Unfortunately, that did not work
Unfortunately that did not work.
|
#7
|
||||
|
||||
Saying "did not work" isn't very constructive. You probably got something messed up in your Conditional Formatting.
Attached you will find my testing. Have a try changing grades in column C. "A" already is done, now enter "B" in cell C2 and pull down and see the difference and finally do the same with "C". |
#8
|
|||
|
|||
You're right, I apologize for my vagueness. I see how it works on your example sheet but I cannot duplicate the efforts on my own. I can copy and paste your formula to do exactly what you did but when I try to apply it elsewhere by changing just the simple data I fail. Please see attached sheet.
I am trying to get column J to fill red when a date is not entered 30 days after column I if it has a 1 in column C, 15 days if it has a 2 in column C, and 7 days if it has a 3 in column C. Please see my conditional formatting to see where I am messing it up. |
#9
|
||||
|
||||
So where you messed up was in post #4 where you asked about the grades "ABC" which is text. In column C you have numbers 123 so the formula in CF cannot be C6="1" but must be C6=1 .
If you use double quotation marks it goes without saying that you are dealing with text and as you well know in Excel "1" (text format) is not equal to 1 (decimal format). I leave you the task of fixing the whole formula . PS. forgot about when J6 has a date, does it still need be highlighted if over date ? If so then the formula in CF could be: =AND(J6="",OR(AND(C6=1,TODAY()-I6>30),AND(C6=2,TODAY()-I6>15),AND(C6=3,TODAY()-I6>7))) |
#10
|
|||
|
|||
That worked! That was my issue was putting the numbers in "" instead of excluding. Once a date is entered I want the red fill to disappear which your formula does, so I am officially a go! Thank you so much for your patience and help!
|
#11
|
||||
|
||||
Thanks for the positive feedback, glad having been of some help.
|
Tags |
conditional formatting, formula, rules failing |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula for looking at 1 cell with a date and then comparing it to four date ranges | LearningMom | Excel | 3 | 10-27-2017 12:44 PM |
Need help with using traffic lights in one cell based on date value in a different cell | wmedjaxnjr | Excel | 1 | 04-09-2016 04:22 PM |
calculate date if date entered in cell, do nothing if blank | ConfuddledOne | Excel | 3 | 11-07-2014 09:37 AM |
Make table cell active add date count date | MelHerndon | Word VBA | 3 | 01-21-2013 04:23 PM |
How can I make a cell color RED under the Today's date cell? | Learner7 | Excel | 2 | 07-08-2010 12:52 AM |