Calculating Time in HH:MM:SS

SteCoxy

Hello, I'm hoping someone might be able to help me with calculating time in Smartsheet. We work with production teams in broadcasting, as well as other non-production teams, so calculating time is becoming rather vital, as more and more departments are using Smartsheet for a varying number of use cases: people and resource management, pre/post-production admin etc.

An old colleague of mine actually managed to create a sheet that was able to calculate it down to frames per second (HH:MM:SS:fps), as he was a technical whizz. The workflow is you type in the start and end times of of when an item has been shown (first 2 columns), then in column 4 (Fr_In) it uses a formula:

=IF([Time Code IN (in HH:MM:SS)]14 <> "", ((((VALUE(LEFT([Time Code IN (in HH:MM:SS)]14, 2)) * 90000)))) + (VALUE(RIGHT([Time Code IN (in HH:MM:SS)]14, 2)) + INT(VALUE(RIGHT(LEFT([Time Code IN (in HH:MM:SS)]14, 8), 2)) * 25)) + INT(VALUE(RIGHT(LEFT([Time Code IN (in HH:MM:SS)]14, 5), 2)) * 1500))

to converts the first column's value into numerics. The example on the dark blue row is, in this instance, what 10 hours equates to 900,000 frames.

An equivalent formula is then used to work out the frames per minute numerical value for the output in column 5 (Fr_Out):

=IF([Time Code OUT (in HH:MM:SS)]14 <> "", ((((VALUE(LEFT([Time Code OUT (in HH:MM:SS)]14, 2)) * 90000)))) + (VALUE(RIGHT([Time Code OUT (in HH:MM:SS)]14, 2)) * 25) + INT(VALUE(RIGHT(LEFT([Time Code OUT (in HH:MM:SS)]14, 5), 2)) * 1500))

Then the 2 figures are deducted in column 6 (Fr_Dur) to work out the difference in time duration in frames per second. My colleague then used some helper columns (the last 3 columns) to convert the value in column 6 into hours, minutes and seconds, in each associated column, using the following formulas respectively:

=IF(([Fr_Dur]14 / 90000) < 10, "0" + (INT([Fr_Dur]14 / 90000)), (INT([Fr_Dur]14 / 90000))) + ":"

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500)) + ":"

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25))

I'm in the process of trying to tweak his old sheet, as for this particular use case, the team doesn't need to collate the data as granular as frames but we still need it down to the second (HH:MM:SS).

At first glance, the above might look like it's working the values out correctly but the last 2 rows, in fact, have slightly wrong duration values, by 3 and 2 seconds respectively. I appreciate this does sound pedantic, however, timings in broadcasting are essential!!

I've looked up a number of solutions in the Community Pages and then been wracking my brains trying to apply these suggestions to my problem here but I can't seem to get them to work. I'm hoping someone might be able to assist in simplifying the solution than it is currently.

I've published a version of this if it makes it any easier trying to play with the formulas:https://app.smartsheet.com/b/publish?EQBCT=b181d63d35f2473fb14c1757a0508b10. Any guidance and or pointers are much appreciated.

Paul Newcome

My apologies for the delay again. Things have been moving pretty quickly for me lately, but I am back with (hopefully) a solution.

So basically we have

Start = hh:mm:ss

End = hh:mm:ss

and you want to know the difference between the two. There are two options for the output. Going with your current layout of "hh:", "mm:", and "ss:" in their own columns, we would use something like this...

In the [Start Helper] column:

=(VALUE(LEFT([Start Time]@row, 2)) * 3600) + (VALUE(MID([Start Time]@row, 4, 2)) * 60) + VALUE(RIGHT([Start Time]@row, 2))

In The [End Helper] column:

=(VALUE(LEFT([End Time]@row, 2)) * 3600) + (VALUE(MID([End Time]@row, 4, 2)) * 60) + VALUE(RIGHT([End Time]@row, 2))

Then in the Duration column:

[End Helper]@row - [Start Helper]@row

Finally we take that total and break it out into the separate "hh:', "mm:", and "ss:" columns like so...

hh:=

= INT (Duration@row / 3600) +”:“

mm:=

=INT((Duration@row - INT(Duration@row / 3600)) / 60) + ":"

ss:=

= Duration@row -(我NT(Duration@row / 3600) * 3600) - (INT((Duration@row - (INT(Duration@row / 3600) * 3600)) / 60) * 60) + ":"

Paul Newcome

There is a thread (link below) that has a lot of time based solutions in it. It may take a little digging because as of right now we are up to 5 pages worth of comments and solutions. If you aren't able to find what you are looking for or need help adapting a particular solution, feel free to let me know, and I will try to help as best I can.

I have decided to create a post that consolidates all of the different time based solutions that I have put together.<\/p>

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.<\/p>

I thought it would be helpful to have them all in one place instead of spread out.<\/p>

I welcome all feedback, suggestions, and additions.<\/p>

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.<\/p>

I will also try to post various tips and tricks as I think of them as well such as:<\/p>

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:<\/p>

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))<\/p>

Calculating Time Worked for Employees<\/a><\/p>

Can you calculate time in Smartsheet?<\/a><\/p>

Conversion of timezones (Solved Formula Included)<\/a><\/p>

Date\/Time\/Year<\/a><\/p>

Flagging a Date and Time Overlap<\/a><\/p>

How do I create time of day columns?<\/a><\/p>

Need to create a "shift" column from a time column<\/a><\/p>","bodyRaw":"[{\"insert\":\"I have decided to create a post that consolidates all of the different time based solutions that I have put together.\\nSome of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.\\n\\nI thought it would be helpful to have them all in one place instead of spread out.\\n\\nI welcome all feedback, suggestions, and additions.\\n\\nI'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.\\n\\nI will also try to post various tips and tricks as I think of them as well such as:\\nIt is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:\\n=VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) + IF(CONTAINS(\\\"p\\\", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) = 12, -12))\\n\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662\"},\"insert\":\"Calculating Time Worked for Employees\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc\"},\"insert\":\"Can you calculate time in Smartsheet?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81\"},\"insert\":\"Conversion of timezones (Solved Formula Included)\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795\"},\"insert\":\"Date\\\/Time\\\/Year\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd\"},\"insert\":\"Flagging a Date and Time Overlap\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767\"},\"insert\":\"How do I create time of day columns?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=54f270e0ed1440f6a25de2453119655c\"},\"insert\":\"Need to create a \\\"shift\\\" column from a time column\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-06-08T15:55:08+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68947\/formulas-for-calculating-time","embedType":"quote","name":"Formulas for Calculating Time"}"> https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

SteCoxy

Thank you@Paul Newcome. I did try looking through a number of different suggestions (including the 5 page thread) but I've ended up getting brain frazzled from a very busy week and trying to see if those suggestions could work.

I'd be most grateful if you could try and help me adapt/simplify the solution I've got - particularly to get theFr_In,Fr_Out&Fr_DurandHr,Min&Seccolumns' formulas simpler.

Paul Newcome

Ok. I will take a look when I have more than just a few minutes to focus on it and will get back to you as soon as I can.

SteCoxy

That would be much appreciated, Paul. I've published a copy of the sheet with the columns unlocked if you need access to it:https://app.smartsheet.com/b/publish?EQBCT=b181d63d35f2473fb14c1757a0508b10&_ga=2.68324883.324566985.1620639078-1939750030.1605810674

I know how the current solution works, as it's using the Time Code IN and Time Code OUT values (that are in HH:MM:SS format) to convert time into frames in theFr_IN&Fr_OUTcolumns:90000) * 90000)) / 1500).1hr at 25 fps is 90000 frames.These 2 figures are then deducted from each other in theFr_DURto give you the Frame Rate Duration, which then gets converted into Hours, Minutes & Seconds in the last 3 columns, which are finally then used to convert into a HH:MM:SS format in theDurationcolumn.

I just can't figure out how to simplify it into either Minutes IN & OUT (rather thanFr_IN&Fr_OUT) or whether those columns are even needed and if I just need the last 3 helper columns instead?

Paul Newcome

My apologies for the delay. My weekend ended up being much busier than expected. I will try to take a more in depth look sometime today.

SteCoxy

No need to apologise Paul - I certainly wouldn't be expecting you to look over this at the weekend. No rush required. I much appreciate your time and insight here.

SteCoxy

@Paul Newcome嗨,保罗,想知道如果你有机会但to have a look over the above?

Paul Newcome

Sorry about that. Things have been rather hectic for me. I'll start taking a closer look today.

Paul Newcome

So to make sure I understand...

You want to take the first two columns of time in and time out and then calculate the duration between the two.

Is there a possibility of a date overlap such as in at 11:59:00pm and out at 12:01:30am?

Will your hours always be entered as two digits such as "01" for 1am?

SteCoxy

Don't worry at all Paul - you're doing me a massive favour, so no problem at all :-)

This is hopefully rather simple, in that it doesn't require date overlap - it's purely the duration of either video/audio clips that need to be calculated, rather than time in the usual sense (10am-6pm).

Paul Newcome

Ok. Then the only other thing I would need to know is will the hours always be two digits such as "01" instead of "1"?

SteCoxy

Yes - always 2 digits - this could be 00, 01, 11 etc.

Paul Newcome

My apologies for the delay again. Things have been moving pretty quickly for me lately, but I am back with (hopefully) a solution.

So basically we have

Start = hh:mm:ss

End = hh:mm:ss

and you want to know the difference between the two. There are two options for the output. Going with your current layout of "hh:", "mm:", and "ss:" in their own columns, we would use something like this...

In the [Start Helper] column:

=(VALUE(LEFT([Start Time]@row, 2)) * 3600) + (VALUE(MID([Start Time]@row, 4, 2)) * 60) + VALUE(RIGHT([Start Time]@row, 2))

In The [End Helper] column:

=(VALUE(LEFT([End Time]@row, 2)) * 3600) + (VALUE(MID([End Time]@row, 4, 2)) * 60) + VALUE(RIGHT([End Time]@row, 2))

Then in the Duration column:

[End Helper]@row - [Start Helper]@row

Finally we take that total and break it out into the separate "hh:', "mm:", and "ss:" columns like so...

hh:=

= INT (Duration@row / 3600) +”:“

mm:=

=INT((Duration@row - INT(Duration@row / 3600)) / 60) + ":"

ss:=

= Duration@row -(我NT(Duration@row / 3600) * 3600) - (INT((Duration@row - (INT(Duration@row / 3600) * 3600)) / 60) * 60) + ":"

SteCoxy

Paul you're an absolute superstar. Can't thank you enough for taking the time to help me here. The team in question will be absolutely thrilled they can use this for their time tracking solution.

Paul Newcome

Happy to help, and sorry it took so long. I've had a lot going on lately and wasn't able to devote a lot of time to the more complex solutions.