Time Conversion from Decimal Back to HH:MM

I have successfully converted my Start / End time columns over to decimal format so I can get the Total Outage Time on my sheet. However, I need to be able to convert it back from decimal format to HH:MM time format just for display purposes. I keep the wrong number of minutes (hours are fine). Can anyone assist? I have read all the various time threads on here, but they are very confusing and I just need a quick fix.

The value I get is 58:67, but the correct answer should be 58:55. Any ideas?

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @NickAC,

Give this a try.

=VALUE(LEFT([Total Outage Time (Hrs)]@row , 2)) + ":" + ROUND((VALUE(RIGHT([Total Outage Time (Hrs)]@row , 2)) * 60) / 100)

Hope this helps,

Dave

DKazatsky2

@NickAC,

I am not seeing 2:40 - see screen shot.

Please verify you are using the following.

=VALUE(LEFT([Total Outage Time (Hrs)]@row , 2)) + ":" + ROUND((VALUE(RIGHT([Total Outage Time (Hrs)]@row , 2)) * 60) / 100)

All comments

DKazatsky2

Hi @NickAC,

Give this a try.

=VALUE(LEFT([Total Outage Time (Hrs)]@row , 2)) + ":" + ROUND((VALUE(RIGHT([Total Outage Time (Hrs)]@row , 2)) * 60) / 100)

Hope this helps,

Dave

Corey W.

@NickAC you will need to multiply the .92 by 60 since it represents a fraction of an hour. You can just add that to your existing function.

NickAC

@DKazatsky2 close, but the rounding is bringing it to the nearest 10. For instance, if the start time is 8:00 AM and the end time is 10:34 AM the same day, the total time should be 2:34. It's rounding it to 2:40.

DKazatsky2

@NickAC,

What is the value in [Total Outage Time (Hrs)] in the example above? I don't have your formula so can't replicate based on the start/stop times.

Thanks.

NickAC

Oh sorry, it is 2.57. Here's an updated screenshot:

DKazatsky2

@NickAC,

I am not seeing 2:40 - see screen shot.

Please verify you are using the following.

=VALUE(LEFT([Total Outage Time (Hrs)]@row , 2)) + ":" + ROUND((VALUE(RIGHT([Total Outage Time (Hrs)]@row , 2)) * 60) / 100)

NickAC

Okay, I figured it out. Your formula IS correct, but the 2.57 formula (Total Outage Time (Hrs)) was only showing two decimal places and was rounding it up. I added a ROUNDDOWN formula in front of that and now I also get 2:34!

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.