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

Options
✭✭✭
edited 12/09/19

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:

• edited 08/22/17
Options

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

• ✭✭✭
Options

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?

• Options

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.

• ✭✭✭
Options

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

This discussion has been closed.