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)
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?
Answers
-
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.
-
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)
-
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?
-
Would you ever have more than a 24 hour duration?
-
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.
-
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)
-
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 -
Missed that very first opening parenthesis.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!