Calculating Time difference between Auto Generated Date/Time and Manually Entered Time column

Hello,

I currently have a form that collects a small sample of data and then generates a row on a sheet, and that row has a "created" column using the auto generated date/time stamp.

Someone completes that task and then marks the row "Approved" which generates an approval DATE and then they manually enter a time stamp using the 24HR format (HH:MM).

How can I calculate the time it takes from creation to approval?

Any help would be appreciated!

Luke

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Luke W.

    Unfortunately you are going to find that Smartsheet is not friendly to time calculations (yet? Maybe someday!) I think the issues stem from having automated times recorded as part of the date, but then translated from UTC or wherever the server is located into the regional settings of the user or their organization for display. Until such time(!) as we get a TIME function or a real Date/Time column type this will continue being an issue that is near impossible to get around.

    One possible(!) way to do what you want would be to use the RIGHT function to pull the time from the Created column. However it's not in 24H format and is just text, so further work would need to be done to be able to calculate anything from it.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Luke W.
    Luke W. ✭✭✭✭✭

    @Jeff Reisman

    Thanks for the response. In a bit of digging, I found that it is indeed difficult to do calculations with time right now. I'll see what else I can come with at this point.

    I appreciate the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!