count NETWORKDAYS from "Request Date" column to "Project Completed" check box column
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!
Comments
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It worked!
Thanks!
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Excellent, Thanks
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives