How do I highlight past dates

[LMDeWitt]

Hi,
I conduct audits annually and want to highlight upcoming audit date
that I need to begin working on scheduling. I am working with the las
audit date and need 4 months notice. I have tried to setup conditiona
formatting but get everything but what I want.

Column E lists the last audit date:
8/10/11 (needs to highlight)
5/26/11 (needs to highlight)
4/6/12
11/2/11

Can anyone help??
Thanks! Lauri

 

[zvkmpw]

I conduct audits annually and want to highlight upcoming audit dates

that I need to begin working on scheduling. I am working with the last
audit date and need 4 months notice.

Column E lists the last audit date:
8/10/11 (needs to highlight)
5/26/11 (needs to highlight)
4/6/12
11/2/11

One way is to select column E and use
Format > Conditional Formatting
with "Formula Is"
=TODAY()>=DATE(YEAR(E1),MONTH(E1)+8,DAY(E1))
and click "Format" to select the desired highlighting.

The 8 months is for a year (between audits) minus the 4 months' notice.

Hope this helps.

 

[Claus Busch]

Hi Laurie,

Am Tue, 22 May 2012 15:29:37 +0000 schrieb LMDeWitt:

Column E lists the last audit date:
8/10/11 (needs to highlight)
5/26/11 (needs to highlight)
4/6/12
11/2/11

select column E => CF => Formula:
=AND(E1>0,E1<TODAY())


Regards
Claus Busch

 

[Claus Busch]

Hi Laurie,

Am Thu, 24 May 2012 10:23:40 +0200 schrieb Claus Busch:

select column E => CF => Formula:
=AND(E1>0,E1<TODAY())

I forgot the 4 month.
Try:
=AND(E1>0,E1<TODAY()-121)


Regards
Claus Busch