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

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

Tags:

• 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.

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

• 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.

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

This discussion has been closed.