Convert decimal number to Hours:Minutes

Question

Level 1

9 points

Convert decimal number to Hours:Minutes

As a driver subject to the DOT 70 hour clock I record and monitor my driving activity and store it in iCloud so I always know if I have enough driving time to accept a load. I transpose my driving time from an on board recording device that displays the hours driven in decimal format. For example, 10:24 minutes of driving time displays as 10.4 hours. My objective is to add a column and use a formula that displays a meaningful value my mind can relate to.

In this example I would like 10.4 (cell A1) to display as 10:24 (cell B1). When I input 10.4 into cell A1, what formula do I type in cell B1 to return the value of 10:24 as displayed?

Also, if my recorded drive time is 10.69 (A2), will this same formula return a value that rounds to 10:41 (B2), assuming the calculated value of 10:414 rounds down or up to two places automatically? One minute off either way doesn't matter, but if I share this file I want others to use it without being confused and trying to convert two digit decimals to minutes.

MacBook Pro, macOS High Sierra (10.13.3), 16 GB RAM, 2 TB HDD

Posted on Feb 17, 2018 6:01 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,064 points

Feb 18, 2018 6:44 PM in response to Steve.Knepp

Hi Steve,

Here are three examples. All three use the same formula.

The first leaves cells B2 and B3 set to Automatic, with the units also left at automatic.

The second example has cells B5 and B6 set to Duration, with custom units set to display only hours and minutes. the display is automatically rounded to the nearest minute, but the actual value contained in the cell keeps the extra seconds.

The third example uses the same Duration and Units settings as the second, but adds a Style setting to display as 0:00.

The formula in B2 of the table (and in all the rest, differing only in the row part of the A2 cell reference) is shown below:

B2: DURATION(weeks,days,A2,minutes,seconds,milliseconds)

This can be shortened by removing the unused tokens, but NOT the commas marking where each unit would fit in the formula:

B2: DURATION(,,A2,,,)

Regarding formats:

I do NOT recommend the 00:00 setting if you are entering duration values directly, as Numbers will often mis-recognize such an entry as a time-of-day part of a date&time value, rather than a duration, or will misinterpret the units to be applied to each part. For direct entry of durations, always use the less ambiguous 5h 45m 33s style.

For your case, where the durations are being calculated from a number representing hours and fraction of an hour, none of these display formats should cause issues.

Regards,

Barry

Reply

Answer 2

Yellowbox

Level 7

21,987 points

Feb 17, 2018 11:25 PM in response to Barry

Hi Steve,

A rather more clunky way than Barry's elegant solution.

Step 1. Find the number of whole hours with the INT function.

Formula in B2 (and Fill Down)

=INT(A2)

Step 2. Find the value of the decimal minutes (tenths of a hour).

Formula in C2 (and Fill Down)

=A2−B2

Step 3. Convert the decimal minutes to real minutes and ROUND them to the nearest whole number.

Formula in D2 (and Fill Down)

=ROUND(C2×60,0)

Step 4. Create a Text display.

Formula in E2 (and Fill Down)

=B2&":"&D2

Step 5. Either combine the formulas into a single, complex formula, or hide Columns B, C and D

The result is a Text display and can not be used in calculations.

Regards,

Ian.

Reply

Answer 3

Steve.Knepp Author

Level 1

9 points

Feb 18, 2018 6:45 PM in response to Steve.Knepp

Thanks Barry, worked like a charm!

Reply

Convert decimal number to Hours:Minutes

Similar questions