Results 1 to 9 of 9

Conditional Format to highlight date occuring next month or later

This is a discussion on Conditional Format to highlight date occuring next month or later within the Excel Questions forums, part of the Question Forums category; Hi, Im guessing this is quite simple but it's had me stuck for a while. I want to create a ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    59

    Default Conditional Format to highlight date occuring next month or later

    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?

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,123

    Default Re: Conditional Format to highlight date occuring next month or later

    For cell A1, use conditional format for formula "=A1>EOMONTH(TODAY(),0)"
    Baitmaster G
    wha'ever, I do whad I wan'

    Excel 2010 (work), 2010 (more work), 2007 on Win 7 and 2010 on Win 8 (home), 2003 (a dim and distant memory)

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    59

    Default Re: Conditional Format to highlight date occuring next month or later

    Quote Originally Posted by baitmaster View Post
    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.

  4. #4
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,123

    Default Re: Conditional Format to highlight date occuring next month or later

    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
    Baitmaster G
    wha'ever, I do whad I wan'

    Excel 2010 (work), 2010 (more work), 2007 on Win 7 and 2010 on Win 8 (home), 2003 (a dim and distant memory)

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    59

    Default Re: Conditional Format to highlight date occuring next month or later

    Quote Originally Posted by baitmaster View Post
    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!

  6. #6
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,123

    Default Re: Conditional Format to highlight date occuring next month or later

    type "=TODAY()" into a cell, what value does it return?
    Baitmaster G
    wha'ever, I do whad I wan'

    Excel 2010 (work), 2010 (more work), 2007 on Win 7 and 2010 on Win 8 (home), 2003 (a dim and distant memory)

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Posts
    59

    Default Re: Conditional Format to highlight date occuring next month or later

    Quote Originally Posted by baitmaster View Post
    type "=today()" into a cell, what value does it return?
    18/07/2011

  8. #8
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,123

    Default Re: Conditional Format to highlight date occuring next month or later

    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...
    Baitmaster G
    wha'ever, I do whad I wan'

    Excel 2010 (work), 2010 (more work), 2007 on Win 7 and 2010 on Win 8 (home), 2003 (a dim and distant memory)

  9. #9
    New Member
    Join Date
    Jun 2011
    Posts
    14

    Default Re: Conditional Format to highlight date occuring next month or later

    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?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com