Conditional Format by date

Pankman · Mar 26th, 2002, 02:21 AM

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 · Mar 26th, 2002, 02:28 AM

Quote:

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 · Mar 26th, 2002, 02:35 AM

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 · Mar 26th, 2002, 02:45 AM

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 · Mar 26th, 2002, 02:53 AM

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 · Mar 26th, 2002, 02:59 AM

Quote:

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 · Mar 26th, 2002, 03:03 AM

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 · Mar 26th, 2002, 03:06 AM

Quote:

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 · Mar 26th, 2002, 03:13 AM

Aladin, that is absolutely perfect!! Thank you

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 02:21 AM	#1
Pankman New Member Join Date: Mar 2002 Posts: 7	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

Mar 26th, 2002, 02:35 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 26th, 2002, 02:45 AM	#4
Pankman New Member Join Date: Mar 2002 Posts: 7	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?

Mar 26th, 2002, 02:53 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 26th, 2002, 03:03 AM	#7
Pankman New Member Join Date: Mar 2002 Posts: 7	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?

Mar 26th, 2002, 03:13 AM	#9
Pankman New Member Join Date: Mar 2002 Posts: 7	Aladin, that is absolutely perfect!! Thank you