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. 👍️
Hello, I'm working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
I have an automation that has a condition where I am looking at the column "LEA Interco Supporting Site(s)" and trying to find columns that have the value "Fort Loramie" in them. This works well when "Fort Loramie" is the first item listed in the cell, but it doesn't work when "Fort Loramie" is the second or third value in…
Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026. I have done it on Excel sheets manually, but I think there should be a way to do it with…
