change cell colour according to date.....



Hello I would be very grateful if anyone could help pont me in the right
direction with a formula to do the following:
I have a date in field D9 and I need field I9 fill colour to change to
orange if today’s date is greater than 20 days after the date in D9 and then
turn red if today’s date is greater than 28 days after the date in D9 I hope
this makes sense, I am not to hot at excel at the moment but am trying to

Many Thanks



This can be achieved using Conditional Formatting - see here for more info:

To colour as you require you require two conditions in I9:
first =TODAY()>$D$9+28 set colour as red
second =$I$9>$D$9+20 set colour as orange

You could of course use two cells remote from your workings enter 20 in one
and 28 in the other and use those cell references in the formulae, that way
you can, if required, easily change the number of days difference!

Hope this helps



Bernard Liengme

Click on I9 and open the Conditional Formatting dialog (in Excel 2007
looking in the Styles group on the Hone tab; in earlier versions open the
Format dropdown on main menu).
First use the rule / Formula Is: =TODAY()-D9>29 and format for red
Then add a second rule ? Formula Is: =TODAY()-D9>20 and format for orange

See conditional formatting

best wishes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question