Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Need Some Help Nesting IF Statements

Options
Matt W
Matt W
edited 12/09/19 in Archived 2016 Posts

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

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    edited 12/19/16
    Options

    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.

  • Matt W
    Options

    Thanks Rob. I'll give that a shot. 

     

    -matt

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 12/26/16
    Options

    Rob,


    Can you check the use of DATEONLY?

    Since the November update, it is not needed anymore.

     

    For example:

    =Finish1 - DATEONLY(Finish1)

    returns 0, where before it would return a decimal related to the time of day.

     

    I've been told by someone at Smartsheet that they may figure out a use for it, but for now the function does nothing.

     

    Matt,

     

    Was the original problem resolved?

     

    Craig

     

     

This discussion has been closed.