Welcome!

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?

time formula.PNG

Find more posts tagged with

Comments

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)

Annotation 2019-07-03 104813.jpg

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Anonymous Survey
Hello, I am looking to send a survey to my team, I would like for it to be completely anonymous, is this something Smartsheet can do? If not is there something we can do to make is as anonymous as it can be? Thank you!
Where are the auto-save settings
My sheets are no longer auto saving. When working on a building a sheet, if i hit the ESC button which I do as a habit sometimes, everything I have done since the last save disappears. It did not used to do that. It's incredibly frustrating as I'll be working on changes for 10-20 minutes then hit esc and everything I have…
Sharing form links via a dashboard
I've created a Dashboard with links to all of the forms that employees may need to submit (mileage reimbursements, equipment check out, injury reporting, etc.). If I share the dashboard with an "All User" group will they be able to access and submit all of the forms on the dashboard?