Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Convert hh:mm to decimal number

I have set up a Number spreadsheet to calculate time spent on projects and the results are in hh:mm. How do I conver that to numerical value with one decimal point (I.e. ####.#) Thanks

iMac, Mac OS X (10.7.5), IPHOTO '11 & Canon G16 camera

Posted on

Question marked as Solved

Hi R'

Setting the format to show only one place after the decimal does not set the value in the cell to that precision.

If you are going to sum these values, you may want to use ROUND to adjust the value in the cell rather than just its format.

In the example below, all durations are recorded in hours and minutes. In column B, those values are converted to hours and decimal hours, and formatted to display only one digit after the decimal.

In Column C, the same values from column A are converted to hours and decimal hours, rounded to the nearest tenth of an hour.

The bottom row shows the sums of columns B and C.

Compare the results if one minute is added to each of the eight durations:

So which is 'correct'? It depends.

If you add the numbers displayed in column B, the sum in both cases will be the amount shown in column C.

If you add the numbers contained in column B, the sum will be the number displayed (rounded to one decimal place) in column B, which is also the number you'd get if you added the hours and minutes recorded in column A, then converted that sum (in A10) to hours and decimal hours and displayed the result to the nearest tenth of an hour.

So the Sum in B10 is closer to the actual sum of the durations recorded than the sum in C10, but will usually not be the sum you would get by adding the numbers displayed in column B.

Regards,

Barry

Posted on

My apologies,

use =DUR2HOURS(cell#)

For example: Cell A2 is "15h 23m"

and you want the the decimal numeric value in cell C2

in C2 "=DUR2HOURS(A2)" Numbers would enter 15.4

to have the your decimal preference - go into format (the paintbrush) and "data" and select your decimals preference.

Feb 26, 2018 6:30 AM

Question marked as Solved

Hi R'

Setting the format to show only one place after the decimal does not set the value in the cell to that precision.

If you are going to sum these values, you may want to use ROUND to adjust the value in the cell rather than just its format.

In the example below, all durations are recorded in hours and minutes. In column B, those values are converted to hours and decimal hours, and formatted to display only one digit after the decimal.

In Column C, the same values from column A are converted to hours and decimal hours, rounded to the nearest tenth of an hour.

The bottom row shows the sums of columns B and C.

Compare the results if one minute is added to each of the eight durations:

So which is 'correct'? It depends.

If you add the numbers displayed in column B, the sum in both cases will be the amount shown in column C.

If you add the numbers contained in column B, the sum will be the number displayed (rounded to one decimal place) in column B, which is also the number you'd get if you added the hours and minutes recorded in column A, then converted that sum (in A10) to hours and decimal hours and displayed the result to the nearest tenth of an hour.

So the Sum in B10 is closer to the actual sum of the durations recorded than the sum in C10, but will usually not be the sum you would get by adding the numbers displayed in column B.

Regards,

Barry

Feb 26, 2018 2:24 PM

Hi Barry, I totalled the time in (hh:mm) format then converted that total into a time in decimal number. Just like how you mentioned it at the end converting the A10 number to get a more accurate result.

Thanks Barry.

Ron

Feb 26, 2018 3:53 PM

User profile for user: RSH888

Question: Convert hh:mm to decimal number