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