Sign in to join the conversation:
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. 👍️
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…
I am trying to send non-Smartsheets users information on forms. I know I can create a publicly accessible and editable link for the overall sheet/form, however, when I set up automated emails with links to the forms, the links to sheets/rows still require a login. Is there any way to get around it so that emails sent…
This discussion was created from comments split from: Generate documents from row data.
