Conditional Formatting

I cannot get conditional formatting in Excel 2010 to work on this formula.

=IF(TODAY()-F73<10,"",TODAY()-F73)

I have this formula entered to calculate the number of days that have passed since the last event. What I need is conditional formatting to highlight the cell where this formula is if the result of the formula is >29. So anything event that is 30 days past or more should be formatted.

When I try the conditional formatting in Excel every cell is highlighted even if the result of the formula above is less than 30. I know there is something screwy about conditional formatting and formulas but I cannot remember and I have not been able to get this to work.
gactoAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Change formula to:

=AND($G2<>"",$G2>29)

Calculation in column G puts "" in cell when less than 10. Excel doesn't recognise as a number so can't evaluate aginst 29 .

Thanks
Rob H
0
 
andrew_manCommented:
Post your sheet here
0
 
gactoAuthor Commented:
Here is a copy of what I am trying to make work. I just used the basic conditional formatting function here but I have tried several times to use other options and all of the results are the same.
ConditionalFormattingExample.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
andrew_manCommented:
0
 
andrew_manCommented:
I suggest that not yet due to be zero instead of ""

=IF(TODAY()-F2<10,0,TODAY()-F2)
0
 
Rob HensonFinance AnalystCommented:
I was thinking the same but left it with user's original.

If you use andrew_man's suggestion you can then use sheet options to hide zeros or Custom Format specific column so that they don't show.
0
 
andrew_manCommented:
Yes
0
 
gactoAuthor Commented:
andrew_man,

the spreadsheet you uploaded works fine until I enable editing and then the formatting fails. I have followed the logic of your formatting and it does not work in my version of Excel.
0
 
gactoAuthor Commented:
Excellent suggestion Rob H.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.