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

axelrodo

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?

Brett Wyrick

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?

Brett Wyrick

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?

axelrodo

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

axelrodo

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?

axelrodo

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?