Formula for difference in timestamps
It is my understanding there still is not a formula to calculate time. Any word on when smartsheet will be getting that functionality?
Is there anyone who may be able to help me with a work around?
We are using a form to dispatch jobs. When the form is submitted it auto populates a row that will be timestamped. When the job is completed we have set up a checkbox and once that box is checked it timestamps and locks the row from future updates (using the last modified date timestamp for this one). Is there any way I can pull the difference in time, not days, between the two timestamps? I have even used =RIGHT([column_name]1, 7) to pull just the time, but can't figure out a way to calculate between the two while still taking into account AM and PM. Any help or suggestions?
Comments

Here's a formula that's been running around that will solve your issues. I didn't create it nor can I find the original author of it to give credit.
=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2))))  INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2))))  INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

I would suggest using multiple helper columns. 3 for each time plus a handful more for the calculations.
Use VALUE(MID(...............)) to pull the hour into one column and the same to pull the minutes in the other column. I would then use the RIGHT function to pull the AM or PM.
Time calculations are much easier honestly when converted to a 24 hour time. The easiest way to do this is to us an IF statement where you simply add 12 to the hours if it is PM.
From there you can multiply the hours by 60 to convert to minutes and add the minutes column.
Once you have done that for both the start and finish times, you can subtract the start from the finish to give you your answer in minutes.
Using the INT function, divide this result by 60 to give you the hours.
Then subtract the hours times 60 from the total time to give you the remaining minutes.
You can then pull those two results together using a basic
=[Hours Calc]@row + ":" + [Minutes Calc]@row
to convert your time difference back to HH:MM format.
If you would like some help with the details, feel free to let me know. I know that there are quite a few time calculation workarounds here in the community, but I will be back on Friday morning for more specific instruction if you'd prefer.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Planned start: 08/11/2022 10:00:00 Actual Start: 08/11/2022 20:28:31. I share the pain  all I want is the difference and to highlight if <> 30 minutes from plan.
I admire the skill of Paul Newcome but am horrified by the need to do such alchemy.
I am now at the point of despair and considering taking back in to excel to calculate it, (beer is attractive too!). I really wish SmartSheet would solve this pain point which has been open for years now.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives