Date Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to put conditional formatting into a cell where I want excel to change
the color of the date in the cell if it date is anywhere between today's date
and 2 months prior to today's date.

So if today's date is August 12th and the date in the cell is anywhere
between June 12th and August 12th the cell will change color. I am using
Excell 2003

Thanks very much for your help.

Best regards,

Dee
 
Hi Dee,

Try the following:
- Select the cell(s) you wish to conditional format and select conditional
formatting from the menu.
- Change condition to 'Formula Is' (rather than 'Cell Value Is')
- Paste this formula into the text box:
=AND(B1<=TODAY(),IF(MONTH(TODAY())>2,B1>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),B1>=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY()))))
- Replace the occurrences of B1 with the cell that is currently active (you
should be able to see this in the worksheet / formula bar behind).
- Select your formatting options and click OK.

Let me know how you get on!

Cheers,

Mark
 
Use a formula of

=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))<A1<=TODAY())

where A1 is the first cell to be CFed.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
=AND(A1<=TODAY(),A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())))
should work. However, you should be able to use the EDATE function instead
of the DATE function to determine 2 months prior. I can't seem to get it to
work though. When I try it I get a "You may not use references to other
worksheets or workbooks for conditional formatting criteria." I don't see
the reference to the other sheet or book though. But the first formula I
gave works.

Thanks,
Bill Horton
 
Hi Mark,

It worked! You guys are awesome! If I wanted to extend the conditional
formatting to include not only 2 months prior but also 2 months in advance,
how would I do that. What I'm trying to do is be alerted to retest dates and
some of the dates have already past and I would like to be alerted if a
retest date is coming up within 2 months.

Thanks again for all your help. I learn so much from you guys.

Best regards,

Dee
 
=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),A1<=DATE(YEAR(TOD
AY()),MONTH(TODAY())+2,DAY(TODAY())))


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
It worked! Thank you so much!

Bob Phillips said:
=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),A1<=DATE(YEAR(TOD
AY()),MONTH(TODAY())+2,DAY(TODAY())))


--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top