Need Some Help Nesting IF Statements

Matt W

Hi everyone, 

 

I am trying to use End Date, Duration, and % Complete to calculate the correct color ball in the status column and I am having trouble combining the statements I believe I need (might be wrong with my basic statements also).

 

Here is my thinking:

Red – if you are at OR past the end date AND not 100% complete

=IF(AND(TODAY() – [End Date]# >= 0 , IF([% Complete]# < 1 , “Red”)))

Yellow – in danger – less than 75% complete AND the end date is within 25% of the duration

Duration = 20 days

End date is within 5 days LATER

=IF(AND(TODAY() – [End Date]# <= [Duration]#*0.25 , IF([% Complete]# < 0.75 , “Yellow”)))

Green – In process – Less than 100% complete AND end date is further out than 25% of the duration

Duration = 20 days

End date is more than 5 days away

=IF(AND(TODAY() – [End Date]# > [Duration]#*0.25 , IF([% Complete]# < 1 , “Green”)))

Blue – before OR on OR after the end date AND 100% complete

=IF(AND(TODAY()-[End Date]# >= 0 , IF([%Complete]# = 1 , “Blue”)))

 

Combine all these statements:

 

=IF(AND(TODAY() – [End Date]# >= 0 , IF([% Complete]# < 1 , “Red” , IF(AND(TODAY() – [End Date]# <= [Duration]#*0.25 , IF([% Complete]# < 0.75 , “Yellow” , IF(AND(TODAY() – [End Date]# > [Duration]#*0.25 , IF([% Complete]# < 1 , “Green” , IF(AND(TODAY()-[End Date]# >= 0 , IF([%Complete]# = 1 , “Blue”))))))))))))

 

Another question is, can I use a calculation of Duration for a less than or equal to statement?

 

Thanks in advance for your help. 

 

-matt

Rob Hagan

Hi Matt,

 

Not quite sure of the specification of your problem, but one thing that I have found through (bitter) experience is that dates in SmartSheet have a time attached. I've found that for elapsed durations (e.g. e2d) the time starts from midnight and for normal durations the time starts from what appears to be 8am (sort of the start of a working day). Net effect is that I use DATEONLY(date) in all of the computations and now they work correctly, even quite complex date computations. The DATEONLY() function throws away any time component. I hope that this assists you on one part of your journey.

 

As to the actual logic above, it looks like the embeded IF operators are superfluous.

Where you write IF(AND(TODAY()-[End Date]# >= 0 , IF([%Complete]# = 1 , “Blue”))) a more appropriate expression would be IF(AND(DATEONLY(TODAY())>=DATEONLY([End Date]1) ,  [%Complete]1 = 1 ), “Blue”, <nextExpression>) in row 1. You then nest these together to achieve your desired outcome.

 

Cheers,

Rob.