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

Question:

Question: 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

Reply
Question marked as Solved
Answer:
Answer:

Hi R'


One added note:


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.

User uploaded file

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

User uploaded file

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

Feb 26, 2018 6:30 AM in response to Yellowbox In response to Yellowbox

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

Reply Helpful
Question marked as Solved

Feb 26, 2018 2:24 PM in response to RSH888 In response to RSH888

Hi R'


One added note:


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.

User uploaded file

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

User uploaded file

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

Reply Helpful
User profile for user: RSH888

Question: Convert hh:mm to decimal number