Share via

Want cell to fill color if date is more than 1 year past

Anonymous
2017-02-14T16:54:00+00:00

How can I get the cells in a workbook to fill in once a date is over 1 year expired? Thank you.

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

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2017-02-14T17:17:45+00:00

    Select the cell or cells with dates.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Select 'Format only cells that contain'.

    Leave the first dropdown set to 'Cell Value'.

    Select 'less than' from the second dropdown.

    In the box next to it, enter the formula   =EDATE(TODAY(),-12)

    Click Format...

    Activate the Fill tab.

    Select a color.

    Click OK, then click OK again.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2017-02-14T18:43:13+00:00

    Hi,

    Based on Hans' formula: =EDATE(TODAY();-12)*(A1>0)

    Excel is considering an empty cell compared to 0 as equal and dates are internally just numbers and a 0 is in this case equal to 01/0/1900 or 12/31/1899.

    By adding *(A1>0) only "valid" cells are taken in consideration.

    5 people found this answer helpful.
    0 comments
  3. Anonymous
    2017-02-14T17:26:31+00:00

    Try this

    best wishes

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2017-02-14T21:32:05+00:00

    Thank you so much!

    0 comments
  5. Anonymous
    2017-02-14T17:33:12+00:00

    This is mostly helpful, however when I apply this to multiple cells before a date is entered, all cells are filled. Can it be conditionally applied to only cells with dates beyond one year? Thank you for your assistance!

    0 comments