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!

Find more posts tagged with

Forms

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.