Formula for difference in timestamps

BSullins

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? 

VAcePro2

BSullins

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)

 

Paul Newcome

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.

NJCunliffe

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.