Time Lapse Calculation
Hello,
I checked the community threads and cannot find a formula that would work.
I need help with a formula to calculate the number work days elapsed from the project start date and today. If the task is marked completed under the status column or checked done, stop calculating the days.
Thank you for your help.
Comments
-
What you're asking for might be possible using a combination of automation, the system last modified column type and formulas.
- Create a new column that uses a system date/time with the modified date type.
- Set up this formula in your time lapse column
=IF(Done@row = 1, NETDAYS([Start Date]@row, Modified@row), NETDAYS([Start Date]@row, TODAY()))
- Create an automation to lock down the row when the Done checkbox gets checked so that the row isn't modified. Any row changes after that point will trigger the System date to update.
This would place the date of the Last Modified date into the time lapse formula instead of today().
-
Did that suggestion work for you? Let me know if you have any questions.
-
If further edits to the row are needed after Done is checked, then you would need to modify Mike's solution slightly to have the end date manually entered (which would also mean you wouldn't want to lock the row either).
I have heard that time calculations are coming soon to Smartsheet (I look for the release to be announced at this year's ENGAGE), but I am not sure if that would also include timestamps on specific actions becoming available as well.
-
I agree with Paul. We moved away from checkboxes to having users enter the actual date they completed the project. This prevents further edits to the row form affecting the modified date. I have seen some hopeful communications that we might be able to calculate the date a specific cell was changed, but we'll have to see whether that becomes a reality or not. I recommend submitting a product enhancement request and adding your vote for the ability to display the date a specific cell was last changed.
-
If I am not mistaken, I believe there is a way to pull the timestamp for a specific action either through the API or through Zapier. I ran into a roadblock on Zapier that I am awaiting feedback on, and I am only slightly familiar with API's. I have been dabbling in Postman, but I have absolutely zero experience with any of that other than the little bit of playing around I have done on my own.
-
Hi,
The third-party service/integration Zapier is an excellent option for this scenario. Is that an option for you?
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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.
-
You're correct, and Zapier would be an excellent solution for this use case.
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.
-
Thank you everyone for your reply. My apologies for the late response - I've been out of the country the past 3 weeks.
Based on your feedback, I replaced the Done column with Complete Date and used the formula below:
=NETWORKDAYS([Start Date]@row, IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY()))
Thank you again for your help!
-
Excellent!
Happy to help!
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.
-
Excellent. I'm glad we could help you out with a working solution!
-
-
I modified the formula to calculate the time lapse between the due date and complete date. However, I'd like for the time lapse to return zero if the complete date is equal to or less than the due date.
So far, I have this:
=NETWORKDAYS([Due Date]@row, IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY()))
-
Hi Paul, Thank you! The formula works, except when complete date is blank, the result is zero instead of calculating the working day from the due date and today.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives