Time to Complete a Task Less Time Wasn't In Lab's Possession

Austin Kuhn · 12/02/20

Hello all,

I am struggling to get this formula correct for "Lab Time to Complete" column. Here's what I need to have done. I believe it's possible, but I keep getting errors and think I may be quite a ways off the mark.

If "Destructive Testing Pending Days" and "Third Party Pending Days" are empty, find networkdays between "Lab Start Date" and "Lab Completed" + "Lab Pending Days"

If "Destructive Testing Pending Days" and/or "Third Party Pending Days" are not blank, sum these one or two numbers and subtract from networkdays between "Lab Start Date" and "Lab Completed" + "Lab Pending Days".

Essentially what I'm trying to do is calculate the total number of days it took the lab to complete an FER (think of it like a mini-project). Destructive Testing Pending days (sometimes not needed/applicable) and Third Party Pending Days (sometimes not needed/applicable) should not count against the "Lab time to complete" and certain FERs/projects require "Destructive Testing" and/or "Third Party" analysis while most do not. Does this make sense? I appreciate the help I've received so far in this community, it's been wonderful, and appreciate anyone that can help here. I think my attempt at this formula is so far off I won't even post it here :/

Genevieve P. · 12/03/20

Hi @Austin Kuhn

Thank you for clarifying! And for correcting the column names, my apologies for missing some of the words. I've created a test sheet and updated it to be the same names as yours, so I'm happy enough working with them as-is.

I pasted in your formula above and it looks like you just "closed off" the IF statement too early... I could tell because the colour of the opening IF( <parenthesis was showing up as a colour of a ) <closing parentheses far too early in the formula. That colour should only be at the very end.

Try this, all I did was remove the extra )'s :

=IF(AND([Destructive Testing Request Pending Days]@row = "", [Third Party Pending Days]@row = ""), SUM(NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row), [Lab Pending Days]@row), NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row + [Lab Pending Days]@row) - SUM([Destructive Testing Request Pending Days]@row, [Third Party Pending Days]@row))

You won't get an error if one of the cells is blank, the SUM will read the blank cell as 0 and won't add anything on to the other value.

Let me know if this works for you, and if you're getting the desired result!

Cheers,

Genevieve

Genevieve P. · 12/03/20

Hi @Austin Kuhn

I'm happy to help you with this! I'll break it down for each statement, then have the full formula at the end of the post.

If both [Destructive Testing Pending Days]@row and [Third Party Pending Days]@row are blank (or equal "")

=IF(AND([Destructive Testing Pending Days]@row = "", [Third Party Pending Days]@row = ""),

Then return the NETWORKDAYS:

NETWORKDAYS([Lab Start Date]@row, [Lab Completed]@row),

Otherwise, if there is any content in either of those two cells, find the NETWORKDAYS between the start and COMPLETED + PENDING

NETWORKDAYS([Lab Start Date]@row, ([Lab Completed]@row + [Lab Pending Days]@row))

Then minus the SUM of those two cells:

- SUM([Destructive Testing Pending Days]@row, [Third Party Pending Days]@row))

Full Formula:

=IF(AND([Destructive Testing Pending Days]@row = "", [Third Party Pending Days]@row = ""), NETWORKDAYS([Lab Start Date]@row, [Lab Completed]@row), NETWORKDAYS([Lab Start Date]@row, ([Lab Completed]@row + [Lab Pending Days]@row)) - SUM([Destructive Testing Pending Days]@row, [Third Party Pending Days]@row))

Here are some Help Center articles I used to build this:

Let me know if this works for you!

Cheers,

Genevieve

Austin Kuhn · 12/03/20

Hi Genevieve,

Thanks for the timely response. I think we are getting closer, I may not have properly stated my intentions of this formula in the first post, but I think we are very close with the formula provided although it is still giving me error messages.

If(AND([Destructive Testing Requesting Pending Days]@row = "", [Third Party Pending Days]@row = "", SUM(NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row), [Lab Pending Days]@row))

This formula above checks to see if both "Third Party Pending Days column AND Destructive Testing Pending Days column's are empty, and if true returns the sum of Lab Pending Days and NETWORKDAYS between Lab Start Date and Lab Complete Date. This seems to be working well. I think I am getting confused on if I can do an "If, Then, Else" type of statement. I think that's what I need? So, if the above state returns false, I need another formula to then subtract the sum of Destructive Testing Request Pending Days@row and Third Party Pending Days@row from the above formula. Does this subtraction formula return error if either Destructive Testing Requesting Pending Days@row or Third Party Pending Days@row are blank? Can you comment on the formula below. I thought it would work properly but am getting an error.

=IF(AND([Destructive Testing Request Pending Days]@row = "", [Third Party Pending Days]@row = ""), SUM(NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row), [Lab Pending Days]@row)), NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row + [Lab Pending Days]@row)) - SUM([Destructive Testing Request Pending Days]@row, [Third Party Pending Days]@row)

The above formula is giving me a syntax error. Appreciate your time and help greatly!

Genevieve P. · 12/03/20

Hi @Austin Kuhn

Thank you for clarifying! And for correcting the column names, my apologies for missing some of the words. I've created a test sheet and updated it to be the same names as yours, so I'm happy enough working with them as-is.

I pasted in your formula above and it looks like you just "closed off" the IF statement too early... I could tell because the colour of the opening IF( <parenthesis was showing up as a colour of a ) <closing parentheses far too early in the formula. That colour should only be at the very end.

Try this, all I did was remove the extra )'s :

=IF(AND([Destructive Testing Request Pending Days]@row = "", [Third Party Pending Days]@row = ""), SUM(NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row), [Lab Pending Days]@row), NETWORKDAYS([Lab Start Date]@row, [Lab Complete Date]@row + [Lab Pending Days]@row) - SUM([Destructive Testing Request Pending Days]@row, [Third Party Pending Days]@row))

You won't get an error if one of the cells is blank, the SUM will read the blank cell as 0 and won't add anything on to the other value.

Let me know if this works for you, and if you're getting the desired result!

Cheers,

Genevieve

Austin Kuhn · 12/03/20

https://community.smartsheet.com/discussion/comment/270623#Comment_270623

This appears to be functioning properly, I will have to use these formulas and reverse engineer them so I can get better with these formulas and how to implement them. I look forward to continue to use smartsheet and learning all of it's ins and outs. Powerful stuff! Appreciate your time and help Genevieve!

Genevieve P. · 12/03/20

No problem at all! I'm glad we could get it working for you. Please feel free to post again in the Community if you need more formula creation help or if you want us to help break down what a currently formula is saying/doing.

Cheers!

Genevieve

Time to Complete a Task Less Time Wasn't In Lab's Possession

Best Answer

Answers

Help Article Resources

Categories