Share via

Help with Prioritizing Excel Conditional Formatting

Kelly, Alexa F 0 Reputation points
2026-01-21T15:27:59.71+00:00

Hello all,

I was asked to create a spreadsheet in which the dates entered in different cells cause them change colors.

The first thing asked was to have the cells in Column G turn red when the date entered is older than today's date, turn orange when within 15 days of today's date, and remain unchanged when after/not within 15 days of today's date.

I was able to do this just fine with conditional formatting.

User's image User's image

However, I was also asked to have this formatting go away when a date entered in Column H is before the date entered into Column G.

ie. If today's date is 1/21/26, the date entered in Column G is 1/30/26 (making it orange), but then 1/15/26 is entered into Column H, I want the orange color of Column G to go away. If the date entered into Column H is 2/1/26, I want the color formatting to remain in Column G.

I've tried playing around with this, but I cannot get all of the conditions to behave the way I want.

I've tried adding a condition to remove cell formatting when the date value in Column G is greater than the date value in Column H. Placing it above the other formatting in the rules manager does not remove the formatting.

User's image User's image

Marking this rule Stop if True does remove the formatting, but then it also removes the formatting in Column G for all subsequent rows that do not have a date filled in Column H yet, which I do not want.

User's image User's image

Putting this rule below the other two rules does nothing to remove the formatting, whether Stop if True is marked or not.

User's image

User's image User's image

Playing around with the order/stop if true marks of these rules doesn't have the results I want, which is the color formatting in Column G to stay when Column H is empty/has a date after the date in Column G, and for the color formatting to go away when Column H has a date before the date in Column G.

I am probably overthinking this, but does anyone have a solution that would satisfy these conditions? The conditions themselves probably have to be altered.

As an extra bit of info, Column G itself is formulated (the date that is entered there is automatically populated in response to a date entered into a previous column), so if a formula is needed in Column G for this formatting to work, it will probably interfere with this formula.

Any help is appreciated. Thank you!

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
  1. Hendrix-C 10,115 Reputation points Microsoft External Staff Moderator
    2026-01-21T16:54:28.29+00:00

    Hi @Kelly, Alexa F,

    Thank you for posting your question in the Microsoft Q&A forum.

    According to your concern, if the formatting required additional date comparison with column H, you need to set up an additional condition to your current existing conditional formatting rules. Based on your sharing, you can try using these two conditional formatting rules:

    • For the Red rule, select the applied range > Condition formatting > New Rule > use a formula to determine...
    • Then use this formula: 
        =AND(ISNUMBER($G2),$G2<TODAY(),OR($H2="",$H2>=$G2))
    • Set the fill color to your preference and click OK to apply the rule

    User's image

    • Next is the orange rule, now you select New rule in the Conditional Formatting Rules Manager dialog box > also choose Use a formula to determine...
    • For orange, you use this formula: 
        =AND(ISNUMBER($G2),$G2>=TODAY(),$G2<=TODAY()+15,OR($H2="",$H2>$G2))
    • Set the fill color to your preference and click OK to apply the rule. At the end you will have two conditional formatting rules like this:

    User's image

    • These rules require column G stores real dates, not text. However, based on your sharing that column G is formulated to import the date values and the comparison with TODAY() function in your original response works properly, there should be no problem.

    User's image

    I hope this information is helpful. You can try workaround and notify me whether it helps, or I've misunderstood anything about your situation. If it doesn't, we can work together to find a solution.

    If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.  

    Thank you for your patience and understanding. Looking forward to your response.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.