Conditional Formatting

[LoneStarAggie]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to highlight a cell one month prior to the date in the cell. I can highlight it using conditional formatting if it is greater than the current date but not sure how to highlight the cell one month before. Any suggestions? Thanks in advance.

 

[Laroche J]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to highlight a cell one month prior to the date in the cell. I can
highlight it using conditional formatting if it is greater than the current
date but not sure how to highlight the cell one month before. Any suggestions?
Thanks in advance.

Let's say your reference date is in E1, and your dates to compare and
highlight are in column G starting at line 1. Select G1 to the last date to
compare, and enter this conditional formatting formula:
=DATE(YEAR(G1),MONTH(G1)+1,DAY(G1))=$E$1

However, what is for you one month prior to March 31st? With the formula
above you will not get any highlighted cell if the date's day exceeds the
previous month's number of days.

So if you want to highlight April 30th for both May 30th and May 31st, you'd
have to enter a second formatting condition:
=AND(DAY(G1)<DAY($E$1),DAY(G1)>DAY(G1+1),MONTH(G1+1)=MONTH($E$1))

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial