Solved

Conditional Formatting

Posted on 2013-12-04
9
216 Views
Last Modified: 2013-12-04
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.
0
Comment
Question by:gacto
  • 4
  • 3
  • 2
9 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39695997
Post your sheet here
0
 

Author Comment

by:gacto
ID: 39696080
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39696104
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39696155
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39696163
I suggest that not yet due to be zero instead of ""

=IF(TODAY()-F2<10,0,TODAY()-F2)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39696177
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39696191
Yes
0
 

Author Comment

by:gacto
ID: 39696433
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
 

Author Closing Comment

by:gacto
ID: 39696436
Excellent suggestion Rob H.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question