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

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(Created@row >= TODAY(+7), "Red", IF(Created@row >= TODAY(+5), "Yellow", "Green")) and,

=IF(Created@row >= Date1 +7, "Red", IF(Created@row >= Date1 +5, "Yellow", "Green")).

None of them work. Can anyone assist please?

Best Answer

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    Answer ✓

    Howdy!

    Try this:

    =IF(Created@row >= TODAY() + 7, "Red", IF(Created@row >= 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.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    Answer ✓

    Howdy!

    Try this:

    =IF(Created@row >= TODAY() + 7, "Red", IF(Created@row >= 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.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • 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(Created@row >= TODAY() + WEEKDAY7, "Red", IF(Created@row >= TODAY() + WEEKDAY5, "Yellow", "Green")) and it doesn't work...any suggesiont?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!