count NETWORKDAYS from "Request Date" column to "Project Completed" check box column

@Help

Hello,

I need help trying to figured out a formula that counts net work days from "Request Date"(Auto-Number Date column) until the actual date but that stops counting when the "Project Completed" (Check Box column) has a check. 

Thanks!

Paul Newcome

To do this you would need a date stamp of when the box was checked. Unfortunately this is not possible within Smartsheet (yet).

 

Options:

Set something up with a 3rd party tool such as Zapier to capture that date in a separate column.

Have the users enter the completed date instead of checking a box.

.

From there it would be pretty straight forward leveraging the two dates to determine the stop point for the NETWORKDAYS function.

Andrée Starå

Hi,

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

As possible workarounds, you could either add the date manually, lock the row to secure the date/time, or use a third-party service like Zapier. 

Would any of those methods work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

@Help

I do have a column "Completed Date" which is a date type. What will be the formula based on this? 

Thanks 

Paul Newcome

Ok. So the basic idea would be a NETWORKDAYS function.

 

=NETWORKDAYS(start_date, end_date)

.

We know we want our start date to be [Request Date]@row

=NETWORKDAYS([Request Date]@row, end_date)

.

To input the end date, we can use an IF statement that will basically say that if [Completed Date]@row is a date, use that, otherwise use TODAY().

 

IF(ISDATE([Completed Date]@row), [Completed Date]@row, TODAY())

.

Once we drop that in the "end_date" portion of the NETWORKDAYS function, you should be set.

 

=NETWORKDAYS([Request Date]@row, IF(ISDATE([Completed Date]@row), [Completed Date]@row, TODAY()))

@Help

It worked!

Thanks!

Paul Newcome

Happy to help! 

Anthony Barthelemy

@Paul Newcome where would I place Networkdays in my formula so it does not count Non-Working Days? I tried the begining of the Formula and with in the If Statement:

=IF(ISBLANK([Finish Date (Actual)]@row), "", IF(AND(ISBLANK([Start Date (Actual)]@row), ISDATE([Finish Date (Actual)]@row)), "Start Date Missing", IF([Start Date (Actual)]@row - [Finish Date (Actual)]@row < 0, ABS([Start Date (Actual)]@row - [Finish Date (Actual)]@row), 0)))

Paul Newcome

You would use it in place of your date calculations.

[Start Date]@row - [Finish Date]@row

becomes

NETWORKDAYS([Start Date]@row, [Finish Date]@row)

Anthony Barthelemy

Ok, Thanks

Anthony Barthelemy

Ok, one more question. If I wanted to return a blank value in the cell until the Finish Date (Actual) has a date. How would i do that

=IFERROR(NETWORKDAY([Start Date (Actual)]@row, [Finish Date (Actual)]@row), 0)

Paul Newcome

=IF(ISDATE([Finish Date (Actual)]@row), IFERROR(NETWORKDAY([Start Date (Actual)]@row, [Finish Date (Actual)]@row), 0))

Anthony Barthelemy

Excellent, Thanks

Paul Newcome

Happy to help. 👍️