Conditional Format by date

[Pankman]

How do I get a column of dates to turn red if they are more than 3 days older than the current date? I'm sure I've seen the answer to this somewhere on here before but I cant seem to find it

 

[Aladin Akyurek]

On 2002-03-26 01:21, Pankman wrote:

How do I get a column of dates to turn red if they are more than 3 days older than the current date? I'm sure I've seen the answer to this somewhere on here before but I cant seem to find it

Lets say that the dates are in A2:A10.

Activate Format|Conditional Formatting.
Choose 'Formula Is' for 'Condition 1'.
Enter in the formula box:

=A1+3<=TODAY()

Activate Format.
Select red on the Patterns tab.
Click OK, OK.
This message was edited by Aladin Akyurek on 2002-03-26 01:41

 

[Dave Hawley]

Hi Pankman

You will need to use
=A1=TODAY()+3

Or

=A1=TODAY()-3

Depending on your need. If you just use:
=A1<=TODAY()
All dates, equal to or less that the current date will be red.

 

[Pankman]

Thanks for the replies, but they didn't seem to work. You see, I have a userform that people fill in, and one of the fields is date, formatted as "dd mmmm yyyy". When they click the command button at the end, the information goes into a sheet of the workbook. Now this info builds up and builds up, so what I need to do is earmark the ones that are older than others, so if the date in the date field is 3 days old or more, then I want it to go red. Would any of this be helpful in figuring out why it didn't work?

 

[Dave Hawley]

You are probably entering the dates as Text.

Use:

Range("A1")=Textbox1.Value

Just ensure your cells are pre-formatted as the date format you want.

 

[Aladin Akyurek]

On 2002-03-26 01:45, Pankman wrote:
Thanks for the replies, but they didn't seem to work. You see, I have a userform that people fill in, and one of the fields is date, formatted as "dd mmmm yyyy". When they click the command button at the end, the information goes into a sheet of the workbook. Now this info builds up and builds up, so what I need to do is earmark the ones that are older than others, so if the date in the date field is 3 days old or more, then I want it to go red. Would any of this be helpful in figuring out why it didn't work?

Did you try the formula in my edited reply?

 

[Pankman]

Hi Aladin, the formula looks great except that I need the formatting to be for the whole of column A, not just A1. How would I do this?

 

[Aladin Akyurek]

On 2002-03-26 02:03, Pankman wrote:
Hi Aladin, the formula looks great except that I need the formatting to be for the whole of column A, not just A1. How would I do this?

Did I forget to say that you need to select the range of interest before you activate Format|Conditional Formatting? If so, that should be the first step in the procedure I described.

Aladin

 

[Pankman]

Aladin, that is absolutely perfect!! Thank you