Can you use the TIME function to calculate the difference i.e. duration between values in 2 columns?

Hello, I've previously used the many pointers and guidance on the forums for coming up with solutions that help calculate time and duration between columns using a range of different helper columns..

I've never used the TIME function before, but I'm hoping I can use this to try and save having to add multiple helper columns in.

I was hoping hoping to use the TIME function to work out the duration between the time in the END OF SET and START OF SET columns. Using the following formula:
=(TIME([END OF SET]@row) - TIME([START OF SET]@row))
I get the below result in the DURATION column

Using the TIME function help page, I was hoping to add ", 0, 2)" at the end of the formula for it to then change the decimal value into a 12hr clock format and for it to return it as hh:mm. Unfortunately, I get an UNPARSEABLE error code.

My questions are - is what I'm trying to do possible using the TIME function and avoiding having to use extra helper columns? And if so, what am I doing wrong?

Find more posts tagged with

Sheets

Formulas

Time Calculation

Accepted answers

All comments

SteCoxy

Just to add, using the following formula:
=(TIME([END OF SET]@row) - TIME([START OF SET]@row)) * 24 * 60
I get the following result:

and then using the same principle addding ", 0, 2)" at the end of the formula I get an UNPARSEABLE error code.

Paul Newcome

Give this a try (update column names to match your sheet):

=INT((TIME(End@row) - TIME(Start@row)) * 24) + ":" + RIGHT("0" + (VALUE(RIGHT(End@row, 2)) - VALUE(RIGHT(Start@row, 2)) + IF(VALUE(RIGHT(Start@row, 2)) > VALUE(RIGHT(End@row, 2)), 60, 0)), 2)

SteCoxy

https://community.smartsheet.com/discussion/comment/474531#Comment_474531

I think I've misunderstood the capabilities of the TIME function in this case, but as ever Paul, thank you so much for being such an oracle of Smartsheet! You are a whiz and I'm very grateful!

SteCoxy

https://community.smartsheet.com/discussion/comment/474531#Comment_474531

I think I've possibly misunderstood what was possible with the TIME function in this case, but as ever Paul thank you so much for being such an oracle of Smartsheet and formulas! Really appreciate your help! Only thing I've noticed is that there's a few instances where the end time goes past midnight and then the duration isn't as expected, such as:

Is there a way around this?

Paul Newcome

Would you ever have more than a 24 hour duration?

SteCoxy

https://community.smartsheet.com/discussion/comment/474631#Comment_474631

Not in this particular case - it's purely for calculating the duration of a performance at an event (by factoring in what time it started and what time it ends), so never likely to be more than 2 hours, however, in same cases the performances would go past midnight, but the dates aren't important.

Paul Newcome

In that case, it may be easier to skip the TIME function and just force it like so:

=(VALUE(LEFT([End of Set]@row, 2)) - VALUE(LEFT([Start of Set]@row, 2)) + IF(VALUE(LEFT([End of Set]@row, 2)) < VALUE(LEFT([Start of Set]@row, 2)), 24, 0)) + ":" + RIGHT("0" + (VALUE(RIGHT(End@row, 2)) - VALUE(RIGHT(Start@row, 2)) + IF(VALUE(RIGHT(Start@row, 2)) > VALUE(RIGHT(End@row, 2)), 60, 0)), 2)

SteCoxy

I tried that and then got this error:

then spotted some of the elements weren't matching to the column headers.. I amended the formula to this:

=VALUE(LEFT([END OF SET]@row, 2)) - VALUE(LEFT([START OF SET]@row, 2)) + IF(VALUE(LEFT([END OF SET]@row, 2)) < VALUE(LEFT([START OF SET]@row, 2)), 24, 0)) + ":" + RIGHT("0" + (VALUE(RIGHT([END OF SET]@row, 2)) - VALUE(RIGHT([START OF SET]@row, 2)) + IF(VALUE(RIGHT([START OF SET]@row, 2)) > VALUE(RIGHT([END OF SET]@row, 2)), 60, 0)), 2)
and they went coloured (indicating the columns had been referenced properly), but the same error message appeared

Paul Newcome

Missed that very first opening parenthesis.

SteCoxy

https://community.smartsheet.com/discussion/comment/474712#Comment_474712

doh! schoolboy error that - thanks so much for your help as always Paul.

Paul Newcome

Happy to help. 👍️

SteCoxy

Hi @Paul Newcome , following on from this, I've got a query that's looking at working out the time from a different angle..

I'm trying to figure out a formula that references a time value in a column (which is an end time of a recording of a performance) and then deducts 30mins from that time to give you the end of the actual performance.

I've used the following formula: =TIME([Encoder Off By]@row) - 30 which is working for the most part, but unfortunately some of the performances go past midnight, so if there's any that straddle midnight I get a weird format:

Would you have any suggestions on how to get around this?

SteCoxy

Just checking in to see if you've had chance to review my query above @Paul Newcome ? Fully appreciate you might be busy, so no rush at all on this.

Paul Newcome

Take a read through this thread. It is up to 19 pages, but there should be a solution tucked away in there somewhere for you.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

SteCoxy

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p17 - the suggestion on page 17 and here: https://app.smartsheet.com/b/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662 looks like it's what I'm after but I was hoping there might be a way to avoid having to create helper columns and start and end date columns since typically the solution is tracking performances at an event where there's not a need for a start and end date except there might be a few performances that run past midnight. I'm trying to migrate from Google Sheets (which isn't approved at my organisation) to Smartsheet, so want to make it as seamless and with the least data input required as possible.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.