Share via

Conditional Formatting between time periods

Anonymous
2023-06-22T02:05:13+00:00

Hi,

I am trying to change the filler colours on an excel spreadsheet based on compliance dates.

I've tried creating my own formulas with conditional formatting but am having no luck thus far.

We are needing the rules to be:

Red filler for dates over 548 days before today

Red filler for dates over 365 days before today

Yellow filler for dates over 730 days before today

Yellow filler for dates over 548 days before today

Yellow filler for dates between 273 and 365 days before today

Green filler for dates between today and 547 days prior to today

Green filler for dates completed between today and 272 days prior to today

Green filler for dates completed between today and 730 days prior

Could someone please help with this? Thank you!

Microsoft 365 and Office | Excel | For business | 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
    2023-06-22T02:45:35+00:00

    Change A2 to the first cell of the range of you want to apply conditional formatting.

    1.Red filler for dates over 548 days before today

    =A2<TODAY()-548

    Image

    2.Red filler for dates over 365 days before today

    =A2<TODAY()-365

    3.Yellow filler for dates over 730 days before today

    =A2<TODAY()-730

    4.Yellow filler for dates over 548 days before today

    =A2<TODAY()-548

    5.Yellow filler for dates between 273 and 365 days before today

    =AND(A2>=TODAY()-365,A2<=TODAY()-273)

    Image

    6.Green filler for dates between today and 547 days prior to today

    =AND(A2>=TODAY()-547,A2<=TODAY())

    7.Green filler for dates completed between today and 272 days prior to today

    =AND(A2>=TODAY()-272,A2<=TODAY())

    8.Green filler for dates completed between today and 730 days prior

    =AND(A2>=TODAY()-730,A2<=TODAY())

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-06-28T04:56:02+00:00

    You are welcome. You may also give feedback on it.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-06-28T04:48:39+00:00

    This has worked a treat! Thank you so much for your assistance- very very much appreciated!

    Was this answer helpful?

    0 comments