Share via

Excel conditional date format - Greater than Today

Anonymous
2011-05-30T12:08:23+00:00

I've been digging through the NUMEROUS posts about conditional date formatting and I just can't seem to get the right formula.

Quite simply, I have 2 columns, all of column G and column I that I want to highlight in green if the date is greater than today's date.  I don't know why I am finding this so hard, but I am.  More than happy to run 2 sets of rules, one for each column.

So, we'll keep it simple here, I want anything in colum G witha date greater than today to be green.

HELP!

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-05-30T12:34:26+00:00

    Select all of column G by clicking the column header label. Hold down the Ctrl key and click the header label of column I. Click the Home tab's Conditional Formatting, New Rule command.

    Select Use a formula to determine which cells to format from the Select a Rule Type: list and then paste this formula into the Format values were this formula is true: text box,

    =AND(ISNUMBER($G1),$G1>TODAY())

    Click the Format button and select a green Fill. Click OK to save the format selection then OK to save the new Conditional Formatting rule.

    The individual cells in your columns G and I will turn green if the cell in column G contains a date greater than TODAY().

    40+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-05-30T12:31:03+00:00

    Try this....

    • Select columns G and I, with G1 as the active cell

    • Home.Conditional_Format...New_Rule

    ...Select: Use a formula to determine which cells to format

    ...Formula: =G1>TODAY()

    ...Click the format button...set the format you want

    ...Click: OK...OK

    EDITED TO INCLUDE THESE COMMENTS:

    I'd assumed all values would be dates or blanks.

    But...if there could be text in the cells (as Jeeped assumed)

    this formula converts text to zeros and applies the CF test in one step:

    =N(G1)>TODAY()

    Does that help?

    10+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-05-29T19:58:57+00:00
    1. Select whatever cells you wish to add Conditional Formatting to (in this case, the cells you populated with your VLOOKUP).
    2. Click the Home tab's Conditional Formatting --> New Rule command.
    3. Click Use a formula to determine which cells to format.
    4. Type =DATEVALUE($G1)>TODAY() in the Format values where this formula is true: box.
    5. Click Format and select whatever formatting you want for these cells where the date in the field is greater than today's date.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-12-12T20:22:24+00:00

    The simple way would be put the current date (=TODAY()) in one specif cell (in my case V1)... and then the conditional formatting.

    1. Select the range
    2. Conditional Formatting
    3. New Rule
    4. Format only cells that contain
    5. Cell value grater than....

    in my case is the opposite.... i did in green all dates before or equal today

    Hope it helps

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-05-30T13:09:59+00:00

    Thanks for the help and quick responses.  Got my green cells working now.

    0 comments