If date older than today color cell RED

**chococharlie** · 09-18-2008, 02:56 PM

Hi guys,

I've been around excel for a while but never tried building any meaningful sheet until now :-)

I need your help.

Column B is populated with dates only. I want the cell to the right (on column C) to become:

- RED -- if the date in column B is equal to a week from the current date or older (basically older than 7 days from now)
- GREEN -- if the date in column B is greater by 8 days from the current date.

For example:
Today (current date) is Sep-18-2008:

Jan-02-2008 RED
Sep-18-2008 RED
Sep-22-2008 RED
Sep-30-2008 GREEN
Oct-11-2008 GREEN

Thank you!

Choco

**NBVC** · 09-18-2008, 03:05 PM

Select the range in column C to colour and go to Format|Conditional Formatting

Select Formula Is from 1st drop down and enter formula =$B1<=TODAY()+7

where B1 is top most cell to check.

Click Format and choose Red

Click Add

Select Formula Is and enter formula: =$B1>=TODAY()+8

Click Format and choose Green

**chococharlie** · 09-18-2008, 03:16 PM

Sir, you are a genius! Thank you.

healthier · 09-19-2008, 01:23 AM

A very well worthy tip from NBVC. Thanks for that.

**Sandcastle** · 06-02-2014, 04:32 PM

This is a great idea, and I am trying to use it, but I'm coming up short. My goal is:
I have a calendar, and I want all days past to be grey, for example.

I thought it would be a simple edit to the above, but I haven't found it. Any input is appreciated.

Thanks

**FDibbins** · 06-02-2014, 05:54 PM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.