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.

Using multiple functions in a formula

MelG
MelG ✭✭✭
edited 12/09/19 in Archived 2017 Posts

Hi, 

I am using a formula to determine "Planned % Complete" based on start date/end date and todays date. The formula I am using is;

=(MIN(TODAY(), Finish1) - Start1 + 1) / (Finish1 - Start1 + 1)

I want to nest the following formula so that if the start date is AFTER todays date, the "Planned % Complete" will be zero; 

=IF(Start1 <= TODAY(), 0, 0)

Now, I would also like to add an element that removes weekends between the start and end date using the WORKDAY function.

Any suggestions how I can use all three functions in the same cell? Or an easier way to calculate the "Planned % Complete" obtaining the same results as mentioned above?

Regards,

Mel

Comments

  • CanadaJim
    CanadaJim
    edited 08/22/17

    Hi Mel,

    This is actually pretty straightforward.  You can, in general, put functions within functions as long as the output data type is correct.  For example, so long as the output is a number, you can do math with it.

    For your specific case, I would start with the IF statement to address tasks that haven't started.

    Then I would do the logic on the remaining tasks.  For example:

    IF(Start1 <= TODAY(), 0, (MIN(TODAY(), Finish1) - Start1 + 1) / (Finish1 - Start1 + 1))

    The third component of the IF statement is processed if the first condition is FALSE.

    In other words, if the tasks hasn't started yet, you'll get a zero.  Otherwise, you'll get the calculation.

    Just one warning: If your task is 1 day long, you will get an error due to Divide By Zero in your formula.  Let us know if you need help with that, but a hint is to put an if statement in front of your calculation, inside the FALSE component of the first IF statement.  This would be called a "Nested IF".

    Jim

     

  • MelG
    MelG ✭✭✭

    Hi Jim,

    Thank you so much for that. I think I spent so much time looking at it, I was making it more complicated than it needed to be. 

    Any ideas on how I can also work the "WORKDAY" function into the formula so I am excluding weekends between the start date and todays date?

  • Hi Mel,

    You probably want to use the NETWORKDAY function to find the difference in workdays between two dates.

    IF(Start1 <= TODAY(), 0, (NETWORKDAY(Start1,MIN(TODAY(), Finish1)) + 1) / (NETWORKDAY(Start1,Finish1) + 1))

    I'm not really sure why you're using the +1 in the formula, you might not need it unless you're building in an extra day of float or something.

  • MelG
    MelG ✭✭✭

    That is a much smoother formula. Thank you so much for your help.

This discussion has been closed.