Conditional Format to highlight date occuring next month or later

chrissmarlow

Board Regular
Joined
Jun 3, 2010
Messages
59
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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top