Using multiple functions in a formula

MelG

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

CanadaJim

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

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?

CanadaJim

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

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