# 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?

• ✭✭✭✭✭

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!

• ✭✭✭✭✭

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!