Excel Formula

Microsoft Excel 2007
July 22, 2009 at 20:41:00
Specs: Windows XP
In column A1 I have a currency that if it is >0 then I want the date in column B1 to turn red 30 days from the date that was entered. How do I do that???

A1 is the amount owed and B1 is the date that a payment was made or the last letter for payment was sent. If there is an amount still owed after the entered date I need the date to turn red after 30 days to remind me to send another letter.


See More: Excel Formula

Report •


#1
July 22, 2009 at 21:11:21
Try this:

1st – Select cell B1

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=IF(AND($A$1>0,TODAY()>=B1+30),TRUE,FALSE)

Sub Menu Format :
6th - Patterns
7th - Choose Red
8th - Press OK
9th– Press OK

MIKE

http://www.skeptic.com/


Report •

#2
July 23, 2009 at 06:45:01
When I click on conditional formating I get a drop down list giving me the choice of Highlight Cell Rules, Top/Bottom Rules, etc. I do not see where to enter your 4th step Change “Cell Value is” to “Formula Is”

Report •

#3
July 23, 2009 at 08:38:22
Sorry, did not notice you were using 2007.

I do not currently have access to 2007 so am at a loss to do the conversion.

Try selecting New Rule from the drop down Conditional Menu selections that's all I can remember.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 23, 2009 at 09:11:21
Yeah it's "new rule" then "Use a formula to determine which cells to format". Put the formula in as above, should work fine.

HTH


Report •

#5
July 23, 2009 at 09:43:34
I must be missing something. I went to new rule, selected "Use a formula to determine which cells to format". There is no sub menu for patterns or colors and when I select ok, nothing happens. What am I doing wrong.

Report •

#6
July 23, 2009 at 12:38:30
When you entered the formula, there should be a Format button, that will open up the color selections.

MIKE

http://www.skeptic.com/


Report •

#7
July 23, 2009 at 13:05:56
Thank you all for your assistance. It works great

Report •

#8
July 23, 2009 at 17:57:05
Is there a simple way to apply that formula to a bunch of cells without doing it one at a time?

Report •

#9
Report •


Ask Question