Share via

Conditional Format cell with date between 15 & 44 days and more than 45 days from todays date

Anonymous
2011-01-12T21:56:17+00:00

Column I2:I55 has dates I enter based on when paperwork has been mailed out. I'm trying to get 3 CF's to work.

A. I2:I55 to CF if the cell date is 15 to 44 days old from today's date (gray)

B. I2:I55 to CF if the cell date is 45 days old from today's date (red)

C. I2:I55 to CF if J2:J55 has a date entered in it (green)

I've tried the following and haven't had luck. Highlighting column I:

  1. cell value between =today()-15 and =today()-44 [gray fill]
  2. cell value less than or equal to =today()-45 [red fill]
  3. =$J2:$J234234= " " [green]

Any suggestions or help would be wonderful.

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

HansV 462.6K Reputation points
2011-01-13T20:37:02+00:00

You can apply the rule to a multi-column range by making the column reference in the formula absolute:

=AND($I2<=TODAY()-15,$I2>=TODAY()-44)

=$I2<=TODAY()-45

=$J2<>""

Was this answer helpful?

0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-17T22:43:40+00:00

    How can I apply this to the whole column withought having to create a CF for each cell? Seems to me there should be a way but when I use your example the entire column changes to the color the formula determines for (using your example above) $I2. I need $I3, $I4, $I5... to look at their own dates and not $I2 to determine what color they should be.

    Thanks!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-13T22:27:29+00:00

    This helped and worked great. Thank you!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-01-13T18:23:18+00:00

    This helped greatly, but I'm having a few issues still. It keeps changing to I3 or J3 in the CF window after I Apply. How can I keep it constant? Also, some cells turn green when nothing is even in J. I'd like to get these 3 CF's down first, but in the end, I'd love it if I can have the entire Row change color. So again, A. If date in I is between 15 and 44 days old (based on today's date)turn gray. B. If date in I is 45 days or more old (based on todays date) C. If any date in J turn I green

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2011-01-12T22:07:39+00:00

    Try "Use a formula to determine which cells to format" for each of the rules.

    a. =AND(I2<=TODAY()-15,I2>=TODAY()-44)

    b. =I2<=TODAY()-45

    c. =J2<>""

    Was this answer helpful?

    0 comments