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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!