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.

Find more posts tagged with

Comments

Mike Wilday

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().

Mike Wilday

Did that suggestion work for you? Let me know if you have any questions.

Paul Newcome

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.

Mike Wilday

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.

Paul Newcome

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.

Andrée Starå

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

Andrée Starå

You're correct, and Zapier would be an excellent solution for this use case.

Nancy Skoulphong

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!

Andrée Starå

Excellent!

Happy to help!

Mike Wilday

Excellent. I'm glad we could help you out with a working solution!

Paul Newcome

Happy to help!

Nancy Skoulphong

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()))

Paul Newcome

You can just drop it into an IF statement...

=IF([Complete Date]@row <= [Due Date]@row, 0, NETWORKDAYS([Due Date]@row, IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY())))

Nancy Skoulphong

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.

Paul Newcome

=IF(AND(ISDATE([Complete Date]@row, [Complete Date]@row <= [Due Date]@row), 0, NETWORKDAYS([Due Date]@row, IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY())))

Give this a try. It is untested, but I am about 99% sure it should work.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…