Convert decimal back to hh:mm

ATCC Administration

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!

Robert S.

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.