Date Change based on date in another cell

Mandy Priestley 20 Reputation points
2024-01-15T19:42:28.9433333+00:00

Training Matrix Formulation I have set up an automated training matrix but am having issues with one of the formulas. In a cell marked O/S, I would like a formula that would look at another cell with a start date in and automatically turn the cell AMBER from 1-30 days after the start date ORANGE from 31-90 days after the start date and RED after 91 days

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,156 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiajing Hua-MFST 14,500 Reputation points Microsoft Vendor
    2024-01-16T07:43:00.4966667+00:00

    Hi @Mandy Priestley

    Are you comparing Today's date with the start date?

    I suggest you use conditional formatting. Here is a simple sample. Select the cells that you need to apply conditional formatting > click Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

    User's image

    Enter following formulas and set the cell format you need.

    Red: =IF(TODAY()-$B2>=91,1,0)

    Orange: =IF(AND(TODAY()-$B2>=31,TODAY()-$B2<=90),1,0)

    Amber: =IF(AND(TODAY()-$B2>=1,TODAY()-$B2<=30),1,0)


    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.



0 additional answers

Sort by: Most helpful

Your answer

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