Solved In Excel 2010 my formula changes the color of blank cells

February 11, 2013 at 07:12:19
Specs: Windows 7
In Excel 2010 I have a speadsheet listing the dates in which employees have obtained their various certifications. Some cells have no dates, because the employee doesn't have that certification. The certifications are the column headings and they expire at different rates (1yr, 3yr, or 5yr). If there is no date I would like the cell to remain white. I would like the cell to turn red if the certification date has expired. I would like the cell to turn yellow if the expiration date is approaching within 2 months. I am using the following formulas:
=IF(TODAY()>=(M7,12),TRUE,FALSE) -----turns RED
=IF(TODAY()>=(M7,10),TRUE,FALSE) ------turns YELLOW
These fomulas work, however If I have no data in the cells they turn red (or yellow if I delete the red fomula). I would like the cells to remain white if there is no data in the cell (the cells will remain white if the date is farther out than 2 months). Do I need to add another formula or do I need to modify my formulas?


See More: In Excel 2010 my formula changes the color of blank cells

Report •


#1
February 11, 2013 at 09:28:10
I don't understand your formulas.

=IF(TODAY()>=(M7,12),TRUE,FALSE)
=IF(TODAY()>=(M7,10),TRUE,FALSE)


What are the 12 and 10 for? All they do for me is result in a "The formula you typed contains an error" message if I try to put them in a cell or use them as a Conditional Formatting rule.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 11, 2013 at 09:43:03
Sorry I forgot one important part of the formulas they should read:
=IF(TODAY()>=EDATE(M7,12),TRUE,FALSE) ---------------------- RED
=IF(TODAY()>=EDATE(M7,10),TRUE,FALSE) ---------------------- YELLOW

The 12 and 10 are the amount of months after the initial certification date that I want the cell to change color. That part works. It changes yellow 2 months prior to expiration and red at the date of expiration. The problem lies in the fact that the cells will change color if I have no data in the cell. This is an issue as the blank cells far and wide out number the cells with data in them. The end result being a nearly all red sheet. These are set as a conditional formatting rules.


Report •

#3
February 11, 2013 at 10:19:48
✔ Best Answer
Simple way to solve the problem is to add another rule:

=M7=""

Do not give it a color.
In the CF window, Check the box the says "Stop If True"

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 11, 2013 at 10:28:01
Just so you know, =EDATE() REQUIRES a start date, as it will still try to calculate the date even if the cell is blank.

So, if M7 is blank, =EDATE(M7,12) will still try to calculate the date and add 366 to the blank cell.

Use the Evaluate Formula button to check.

MIKE

http://www.skeptic.com/


Report •

#5
February 12, 2013 at 10:51:57
mmcconaghy- Thank you. The formula works perfectly. And thank you for the knowledge. I may not know how to fish right now, but I'm no longer starving and can learn to fish later.

Report •

Ask Question