Hi,
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. I have tried to setup conditional 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! Laurie

Hi Laurie,

Easy enough to set up, but I need to know which version of Excel you're using as it differs slightly between them.

S.

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.

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(E10,E1<TODAY())


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Laurie,

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

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

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2