Set Up a Time Frame formula by which a task needs to be completed

01/05/22
Accepted

Hi everybody, I am trying to create a formula that triggers an orange light or a red light after a certain amount of days has gone by since the creation of the issue.

E.g. an issue is created on January 1.

I have 7 days to solve the issue.

The formula would trigger a green light up to 5 days left (for instance, past the 5th day of my 7 days time frame, I'll go from green to orange and after the 7th day, I'll go into red).

The two formulas I tried are:

=IF([email protected] >= TODAY(+7), "Red", IF([email protected] >= TODAY(+5), "Yellow", "Green")) and,

=IF([email protected] >= Date1 +7, "Red", IF([email protected] >= Date1 +5, "Yellow", "Green")).

None of them work. Can anyone assist please?

Best Answer

  • Brett WyrickBrett Wyrick ✭✭✭
    Accepted Answer

    Howdy!

    Try this:

    =IF([email protected] >= TODAY() + 7, "Red", IF([email protected] >= TODAY() + 5, "Yellow", "Green"))

    I think having a value within TODAY() may be throwing it off, perhaps?

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

Answers

  • Brett WyrickBrett Wyrick ✭✭✭
    Accepted Answer

    Howdy!

    Try this:

    =IF([email protected] >= TODAY() + 7, "Red", IF([email protected] >= TODAY() + 5, "Yellow", "Green"))

    I think having a value within TODAY() may be throwing it off, perhaps?

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

  • Hey Brett, looks like it's working! Thanks heaps!

  • One more thing now that i think about it...how to exclude week-ends from the calculation of the working days, e.g. green goes to orange after 4 working days, then goes to red after 3 more working days?

  • Hi Brett, I tried this to keep week-ends out of the calculation:

    =IF([email protected] >= TODAY() + WEEKDAY7, "Red", IF([email protected] >= TODAY() + WEEKDAY5, "Yellow", "Green")) and it doesn't work...any suggesiont?

Sign In or Register to comment.