Formula For Subtracting Working Days

Austin Kuhn

Hello all,

I am a lab technician that am not currently up to speed on smartsheet functions. Can someone help me in creating a function, in hopes I can reverse engineer to understand it and hopefully not have to ask you all for help next time? I have been meaning to learn more about smartsheet and it's functions, as I see at as an extremely powerful tool, and know I'm only using some generic functions now but hope to increase my knowledge and smartsheet as time goes on.

So, I review failed products. Once I start reviewing product, I input date into "Lab Start Date." Typically I can come to a conclusion at which point I will input date in "Lab Complete date." However, certain scenarios require me to send the product out to 3rd party labs for analysis. For the start date of this process, I input date into "Third-Part Date Out" column. When I receive product back from third-party, I input date into "Third-Party Date In Column." I need to know how to take these days (working days only) which the product is at a third-party site and subtract these days (number/count of days) from the total days (Lab Start Date - Lab Complete Date). Does this make sense? Can anyone help me create a formula to do this? Or get pointed in the right direction, struggling how to figure it out. Appreciate any time or help you can provide.

Genevieve P.

Hi @Austin Kuhn

The NETWORKDAYS function (see here) determines the number of working days between two dates. You could use this function twice (first to find out the total days, then second to find the third party days) and minus one output from the other!

Try something like this:

=NETWORKDAYS([Lab Start Date]@row, [Lab Date Completed]@row) - NETWORKDAYS([Third-Party Date Out]@row, [Third-Party Date in]@row)

Now, if this won't happen for every row, you could embed this formula in two IF statements that say, if the Date Completed is blank, return a Blank Cell.

=IF([Lab Date Completed]@row = "", "",

Then, if the Third-Party Date Out is blank, return only the NETWORKDAYS of the original Lab Dates.

 IF([Third-Party Date Out]@row = "", NETWORKDAYS([Lab Start Date]@row, [Lab Date Completed]@row)

Otherwise perform the first calculation I noted above.

Try this full formula:

=IF([Lab Date Completed]@row = "", "", IF([Third-Party Date Out]@row = "", NETWORKDAYS([Lab Start Date]@row, [Lab Date Completed]@row), NETWORKDAYS([Lab Start Date]@row, [Lab Date Completed]@row) - NETWORKDAYS([Third-Party Date Out]@row, [Third-Party Date in]@row)))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve