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!
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.
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
I do have a column "Completed Date" which is a date type. What will be the formula based on this?
Thanks
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()))
It worked!
Happy to help!
@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)))
You would use it in place of your date calculations.
[Start Date]@row - [Finish Date]@row
becomes
NETWORKDAYS([Start Date]@row, [Finish Date]@row)
Ok, Thanks
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)
=IF(ISDATE([Finish Date (Actual)]@row), IFERROR(NETWORKDAY([Start Date (Actual)]@row, [Finish Date (Actual)]@row), 0))
Excellent, Thanks
Happy to help. 👍️
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto
