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

Options

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 :/


Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Austin Kuhn ✭✭✭
    edited 12/03/20
    Options

    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!

  • Austin Kuhn
    Austin Kuhn ✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!