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. 👍️
Earlier today it looks like several columns of data had the "clear contents" action taken in error. Is there a way to get that back other than using the snapshot feature and manually adding it back to a sheet with conditional formatting etc.? thanks in advance for taking a look1
First of all, I use Smartsheet Gov. I have the following workflow set-up. I wish to add another component to it, but I am not sure how to do it. In addition to the date being recorded as shown, I also want an email sent to a specific address. Oh, and I also need to have the Trigger set so it ONLY works when today's date is…
Hello - This morning when trying to go to Smart sheet University, my peer and I clicked on "help" and nothing happens. It was working fine Friday, but no longer working today. Can't get to help or Smartsheet University. Anyone else having this issue? Used in Chrome and Edge browsers
