formula for determining time duration between CREATED date and MODIFIED fields

Mike.Brown

We log all of the voicemails that come into our office. I am trying to track the time that it takes for each person to respond to each VM. The log each time that they try to contact the customer. I am trying to track the response time which is the time elapses from the date the voicemail was assigned (CREATED) and or example,

CREATED MODIFIED 1st Attempt Date

4/14/25 10:00 am 4/15/25 12:15 4/15/25

The challenge that I am having is that I only want the response time to calculate once when there the first attempt has been made and not continuously.. I got the time difference figured out, but can't get it to only calc once. Thanks for your help

Laurie Olson

I would add a checkbox if there is nothing they are mandated to change. How do they access their Voice Mail? In a report? In the sheet? If they have multiple VMs, how do you know they looked at all of them?

Laurie Olson

I would create the following fields, requiring the person check the box when they view the voicemail.

With these fields in the sheet, you could then create a workflow to populate first access, as follows:

The viewed column is needed for anyone that has not changed any values for the given row but did, in fact, review the voicemail. It is the change that the workflow can flag to kick off.

Mike.Brown

thanks for the reply. I use a dynamic view to have them enter the 1st attempt date. Once they enter a date, it should calc the difference between the created date and modified date. The challenge that I am having is that I only want it to calc once when the initial entry is made. I am not sure that an automation is going to work, because the response time is a calculated field. This is what I am looking for. Say the voicemail was assigned to then on 4/1/25 at 10am. They try and contact the customer at 11. That is an hour response time, but I only want that response time to be calced when the initial entry is made. currently, it is continually calculating. I thought that modified date would work and only update when something was changed, but it seems to change every time the employee opens their dynamic view.

In this example, it was assigned on 4/1/25 and the first contact attempt was 4/8/25 I have no way of knowing but let's say 11 am. The most recent change )modified date) is today. I could just use two dates, but that would only give my the number of days it took to contact the customer, I was trying to determine the number of hours that it took. The challenge that I am having is the row being modified by cell=link@smartsheet.com.. I don't think that modified date is going to work. I am happy to use another helper column, but is there a way to once the 1st attempt has been made, to timestamp another column?

Michelle Choate 2

Hi @Mike.Brown ! Unfortunately the only time stamped columns that are offered in the Smartsheet native client are Created and Modified. You could probably create an archive function that could move the row with the information regarding the first contact to a new sheet after a formula has confirmed the time between VM and first contact. Would that work?

Mike.Brown

Thanks for getting back to me. I did see that copying to another sheet was an alternative, but then we are going to have two identical sheets. The only difference being the modified date. and in the off chance that someone modifies the archive sheet, that is going to negatively affect the response time. It's too bad there is no way to capture the modified date and not have it change when the row is modified down the road. Or at least none that I can find anyway.

Andrée Starå

https://community.smartsheet.com/discussion/comment/481155#Comment_481155

Hi,

I hope you're well and safe!

This might help!

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Mike.Brown

I saw that thread. It just talks about capturing the date with the record a date automation. I lot of the response times that I need to capture are the same day, so it would be nice to be able to say that for example, that on average we respond to voicemails in xx hours. Modified date works great, but it is also modified anytime an automation updates the row which is why I need to take a onetime snapshot of it.