Conditional Format to highlight date occuring next month or later

[chrissmarlow]

Hi,

Im guessing this is quite simple but it's had me stuck for a while. I want to create a conditional format which will highlight a cell with a date which occurs next month or any month after that. Or more simply, any date which occurs after the last day of this month. Can anyone help?

 

[baitmaster]

For cell A1, use conditional format for formula "=A1>EOMONTH(TODAY(),0)"

 

[chrissmarlow]

For cell A1, use conditional format for formula "=A1>EOMONTH(TODAY(),0)"

Thanks for the reply, not sure if im doing something wrong though but this seems to highlight random cells with dates in the past. What I want is, if any cell in column B contains a date occuring from the first day of next month onwards for the conditional format to highlight it.

 

[baitmaster]

Sounds like you've written the CF with a formula referring to A1, into a cell other than A1. This will have the effect of offsetting the CF formula to look at a different cell

Wipe the CF out and start again. Highlight the range of cells you want this applied to. Write this formula in that range of cells, but instead of A1, type the reference of whichever cell is active

 

[chrissmarlow]

Sounds like you've written the CF with a formula referring to A1, into a cell other than A1. This will have the effect of offsetting the CF formula to look at a different cell

Wipe the CF out and start again. Highlight the range of cells you want this applied to. Write this formula in that range of cells, but instead of A1, type the reference of whichever cell is active

Its still not working for me....

I am highlighting the whole of column B, going to CF and adding the forumla, changing A1 to B1 (this is probably where im going wrong) and then setting the formatt, for some reason, it still highlights the date 24/06/2011...

Would it make a difference if I am trying to conditionally format a pivot table?

Thanks again for the help!

 

[baitmaster]

type "=TODAY()" into a cell, what value does it return?

 

[chrissmarlow]

type "=today()" into a cell, what value does it return?

18/07/2011

 

[baitmaster]

I can't see why this doesn't work

I've set it up on a single cell, and tested it for various values. I've then copied the format to a range of cells, and hence tested it for multiple values. It works fine - you might try this single-cell-build-up approach as it should clear any inconsistencies in ranges

I haven't done it for a pivot table. I can't see why that would make a difference, but then Excel nevers ceases to amaze me at times...

 

[awalsh80]

Hi There, I have a similar issue with a conditional format and was hoping for some help:

I have a weekly date range at the top of the sheet that goes from Cells H1-BR1 (7/22/2011 - 8/31/2012)

There are 3 conditions:
1: There will be a start date (1 cell) that I want to be green
2: There will be a range (multiple cells) that I want to be blue
3: There will be a finish date (1 cell) that I want to be red

Example would be:

A2 (Activity 1)
B2 (Start date) 7/22/2011
C2 (Start of Range) 11/18/2011
D2 (Finish of Range) 12/23/2011
E2 (Finish Date) 4/20/2012

I would expect to see Green on cell H2
Blue on cells Z2-AE2
Red on AX2

Is this possible?