conditional formatting - dates

**IanJ** · 12-20-2007, 12:45 PM

I have a list of actual dates against a list of planned dates.
I want to highlight those actual dates that occur in the month after the planned date.

For example, if the planned date is 15 April and the actual date is 28 April - no highlight - BUT if the actual date is 1 May - then it highlights.

Thanks!

**NBVC** · 12-20-2007, 02:31 PM

Select the range of Actual Dates....and go to Format|Conditional Formatting.

Assuming the Planned dates are in A1:A100 and the Actual dates are in B1:B100,

then in the CF window, select Formula Is from 1st drop down and enter formula:

=MONTH(B1)>MONTH(A1) where A1 and B1 contain the first dates in your selected range

then click Format and choose you colour pattern. Click Ok and click Ok again to finish.

Note: This will highlight actual dates that are in the months after the planned date, whether it is 1 month after or 2 or 3, etc...

if you only want dates that are within only 1 month after then use formula:

=MONTH(B1)=MONTH(A1)+1

**IanJ** · 01-03-2008, 12:05 PM

Thanks - just got back to this after the holidays.

This works well for months within the same year, but doesn't work across years. For example, I've expanded the formatting so that later months highlight in red, and earlier months highlight in green. So, Dec 07 is earlier than Jan 08, but highlights in red because Dec is later than January. But what I want is for it to highlight in green, because it's earlier.

In other words, is there a variant of the formula that will cope with the year?

Thanks

**NBVC** · 01-03-2008, 12:39 PM

Would this work?

Please Login or Register  to view this content.

**IanJ** · 01-03-2008, 01:16 PM

Hi - yes, that works - but the effect is now that matching dates also highlight.
Sorry - should have been clearer - dates are in the format dd/mmm/yy and what I need is:
* month A (actual) matches month B (planned) - no conditional formatting
* month A (actual) later than month B (planned) - highlight in red
* month A (actual) earlier than month B (planned)- highlight in green

So is there a tweak to the formula that would ignore matching months?

Thanks

**NBVC** · 01-03-2008, 01:30 PM

Select From A2, downwards and go to Format|Conditional Formatting

Try this for Red:

Please Login or Register  to view this content.

and this for Green:

Please Login or Register  to view this content.

**IanJ** · 01-04-2008, 04:50 AM

Thanks - that works fine for red - any actual dates that fall in the following month to the planned date, or later, highlight in red.

But it doesn't quite work for green - here, dates in the preceding month don't highlight properly.
For example: planned date - 15 May 08; actual date - 15 April 08 - no green highlight (but there should be). Highlight doesn't appear unless date is 1 April 08, or earlier.

I've tried tweaking the formula numbers to solve this 'preceding month' problem, but no success - any ideas?

Thanks

**NBVC** · 01-04-2008, 08:56 AM

I think it's better if you post a sample file and show which dates should be highlighted in which colours....I am getting confused about which date is planned and which is actual.

**daddylonglegs** · 01-04-2008, 09:36 AM

Originally Posted by NBVC

Please Login or Register  to view this content.

This will only format dates 2 months (or more) back

you could make it

=DATE(YEAR(A2),MONTH(A2),DAY(A2))<DATE(YEAR(B2), MONTH(B2),1)

although I prefer

=AND(A2<=B2-DAY(B2),A2<>"")