# 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

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

Question marked as
Solved

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.

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

I did another search and found the answer. Thanks

Hi RSH,

For the benefit of other users, please tell!

Regards,

Ian.

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.

Question marked as
Solved

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.

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

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

User profile for user: RSH888

Question: Convert hh:mm to decimal number