Convert decimal back to hh:mm
Hi,
Any help would be greatly appreciated. I am trying to calculate the following:
Start Work Time (input)
First Break Start Time (input)
First Break Finish Time (input)
Second Break Start Time (input)
Second Break Finish Time (input)
Third Break Start Time (input)
Third Break Finish Time (input)
Work Finish Time (input)
Total Time (calculate)
Total Work Time (calculate)
Total Break Time (calculate)
The input data is coming in from a webform and is in HH:MM format. also some of the break times may be received with no data so it would be blank sometimes.
Please if someone can help with this formula it would be greatly appreciated!
Comments

Hello,
Thanks for the question. This gets to be pretty tricky as Smartsheet currently doesn't calculate time. If you're interested in seeing this as an option, I'd recommend submitting a Product Enhancement Request using the link to the right.
That being said, if the times being put into the input columns are always formatted in HH:MM (and are 24 hour time), you can use formulas to calculate the number of hours in decimals for each of the total columns. More on all of the functions we currently offer can be found here if needed (https://help.smartsheet.com/functions). Here's some examples of how these formulas could be written:
Total Time formula
=(((VALUE(LEFT([Work Finish Time]@row, FIND(":", [Work Finish Time]@row)  1)) * 60) + VALUE(RIGHT([Work Finish Time]@row, 2)))  ((VALUE(LEFT([Start Work Time]@row, FIND(":", [Start Work Time]@row)  1)) * 60) + VALUE(RIGHT([Start Work Time]@row, 2)))) / 60
Total Break Time formula
=(IFERROR((((VALUE(LEFT([First Break Finish Time]@row, FIND(":", [First Break Finish Time]@row)  1)) * 60) + VALUE(RIGHT([First Break Finish Time]@row, 2)))  ((VALUE(LEFT([First Break Start Time]@row, FIND(":", [First Break Start Time]@row)  1)) * 60) + VALUE(RIGHT([First Break Start Time]@row, 2)))), 0) + IFERROR((((VALUE(LEFT([Second Break Finish Time]@row, FIND(":", [Second Break Finish Time]@row)  1)) * 60) + VALUE(RIGHT([Second Break Finish Time]@row, 2)))  ((VALUE(LEFT([Second Break Start Time]@row, FIND(":", [Second Break Start Time]@row)  1)) * 60) + VALUE(RIGHT([Second Break Start Time]@row, 2)))), 0) + IFERROR((((VALUE(LEFT([Third Break Finish Time]@row, FIND(":", [Third Break Finish Time]@row)  1)) * 60) + VALUE(RIGHT([Third Break Finish Time]@row, 2)))  ((VALUE(LEFT([Third Break Start Time]@row, FIND(":", [Third Break Start Time]@row)  1)) * 60) + VALUE(RIGHT([Third Break Start Time]@row, 2)))), 0)) / 60
Total Work Time formula
=[Total Time]@row [Total Break Time]@row
Being that these formulas are rather complex and it sounds like they're going to be on every row for a potentially large sheet, you will likely see slowness occur. The use of @row here will help to keep the sheet more efficient than using the row numbers for these cell references. More on @row can be found here (https://help.smartsheet.com/articles/2476491#row).
Please let me know if you have questions on and of this, and I'll be happy to help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!