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

Find more posts tagged with

Accepted answers

All comments

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.

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.