Forum Discussion

HC1793's avatar
HC1793
Copper Contributor
Mar 12, 2025

Excel Formula Needed

Hello all, 

 

I am in need of an excel formula. I cannot find help on exactly what I am hoping to do, and that could be because I don't know the key words to search it.

I have a document where annual tasks were completed, that date was put into an excel sheet. I need those cells to turn green when it's been 6 months from when the task was done and remain green until it turns yellow when it's been 7-9 months from when the task was done, and then it will turn red when it's been 10-12 months since the task was last done.

 

Any help with this is so very appreciated!

community
  • belzic's avatar
    belzic
    Copper Contributor
    Mar 18, 2025

    Steps to Apply Conditional Formatting:

    1. Select the Relevant Cells:
      • Highlight the cells where you want to apply this formatting (e.g., the dates of tasks).
    2. Open Conditional Formatting:
      • Go to the Home tab, and in the Styles group, click on Conditional Formatting. Then, choose New Rule.
    3. Create the Rules for Each Time Range:

    Rule 1: For Green (6-6.99 months)

      • Select "Use a formula to determine which cells to format."
      • Enter the following formula (assuming the task dates are in column A and start in row 1):
      • =AND(TODAY() - A1 >= 182, TODAY() - A1 < 213)
        • Explanation: 182 days (approx. 6 months) to 213 days (approx. 7 months).
      • Click Format, choose a green fill color, and then click OK.

    Rule 2: For Yellow (7-9.99 months)

      • Add a new rule.
      • Formula:
      • =AND(TODAY() - A1 >= 213, TODAY() - A1 < 304)
        • Explanation: 213 days (approx. 7 months) to 304 days (approx. 10 months).
      • Click Format, choose a yellow fill color, and then click OK.

    Rule 3: For Red (10-12 months)

      • Add another rule.
      • Formula:
      • =AND(TODAY() - A1 >= 304, TODAY() - A1 <= 365)
        • Explanation: 304 days (approx. 10 months) to 365 days (approx. 12 months).
      • Click Format, choose a red fill color, and then click OK.
    1. Finalize and Apply:
      • Click OK to close the Conditional Formatting Rules Manager. Your task cells should now dynamically change colors based on the time elapsed.

     

  • MoritzG's avatar
    MoritzG
    Copper Contributor
    Mar 13, 2025

    Hello,

    To color-code task completion dates:

    Select the task completion date cells.
    Go to Conditional Formatting -> New Rule -> Use a formula to determine which cells to format.
    For Green (6 months ago):
    =AND(TODAY()-A1>=180, TODAY()-A1<210)

    For Yellow (7-9 months ago):
    =AND(TODAY()-A1>=210, TODAY()-A1<270)

    For Red (10-12 months ago):
    =AND(TODAY()-A1>=300, TODAY()-A1<=365)

    After entering each formula, click OK. The cells will color-code based on the time passed since task completion.

    In some environments, it could be that you need to use ; instead of , as seperator.

     

    Hope that helps!

    Good luck and best regards :)

Resources