Time Lapse Calculation

Nancy Skoulphong

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.

 

Mike Wilday

What you're asking for might be possible using a combination of automation, the system last modified column type and formulas.

1. Create a new column that uses a system date/time with the modified date type. 
2. Set up this formula in your time lapse column
   
   
   
   =IF(Done@row = 1, NETDAYS([Start Date]@row, Modified@row), NETDAYS([Start Date]@row, TODAY()))
   
    
3. 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.