excel 2007 formating

February 21, 2011 at 02:15:34
Specs: Windows 2000
i am trying to change the colour of a cell when a set peiod of days has passed the date in the cell.
anyone got any ideas?

See More: excel 2007 formating

Report •


#1
February 21, 2011 at 07:33:51
Your looking for Conditional Formatting,
see if this thread helps:

http://www.computing.net/answers/of...

MIKE

http://www.skeptic.com/


Report •

#2
February 23, 2011 at 03:10:41
Hi
thanks for your quick reply
Conditional formatting does seem to be the answer but i cant get the formula on the suggested thread to work for me.
=IF((D1+14)>=TODAY(),TRUE,FALSE)

As i wish the cell that contains a date to change coulour after 5 days has passed
i changed the above formula to =IF((J44+5)>=TODAY(),TRUE,FALSE)

As J44 is the cell that contains the date.


Report •

#3
February 23, 2011 at 11:47:08
It works for me.

If you have used Condition Formatting on cell J44 then:

If cell J44 contains the date: 2/17/2011
it will not change color.

If cell J44 contains the date:2/18/2011 through 2/23/2011
it will change color.

Perhaps if you explain a bit more about what your trying to do it might help.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 25, 2011 at 02:54:22
Mike,
I have spreadsheet that i record data so when i send pieces of equipment out for repair the company should send me a quotation within 5 days so i enter the date in the cell that i send out the kit and would like the cell to change colour after 5 days has lapsed.
Thanks

Report •

#5
February 25, 2011 at 12:34:29
Your formula almost worked for me.

You just have the greater than or equal wrong

=IF((J44+5)>=TODAY(),TRUE,FALSE)

should be less than or equal

=IF((A1+5)<=TODAY(),TRUE,FALSE)

Just to demonstrate:

If I have the Dates as shown in Column A and take the formula:

=IF((A1+5)<=TODAY(),TRUE,FALSE)

and put it in column B, this is the result.

         A        B
 1) 2/15/2011	TRUE   (Will turn color today)
 2) 2/16/2011	TRUE   (Will turn color today)
 3) 2/17/2011	TRUE   (Will turn color today)
 4) 2/18/2011	TRUE   (Will turn color today)
 5) 2/19/2011	TRUE   (Will turn color today)
 6) 2/20/2011	TRUE   (Will turn color today)
 7) 2/21/2011	FALSE  (Will Not turn color today)
 8) 2/22/2011	FALSE  (Will Not turn color today)
 9) 2/23/2011	FALSE  (Will Not turn color today)
10) 2/24/2011	FALSE  (Will Not turn color today)
11) 2/25/2011	FALSE  (Will Not turn color today)

MIKE

http://www.skeptic.com/


Report •

#6
March 3, 2011 at 02:14:21
Hi
Thanks this now works perfect.
Do you know if it is possible to put an exception in this formula so any cells that dont contain a date stay blank as at the moment they also change colour

Report •

#7
March 3, 2011 at 11:55:09
Try this:

=IF(AND(A1<>"",(A1+5)<=TODAY()),TRUE,FALSE)

MIKE

http://www.skeptic.com/


Report •

#8
March 4, 2011 at 01:10:32
Thanks
Your a legend

Report •


Ask Question