Formula to convert a time duration into hours?

Question

Formula to convert a time duration into hours?

Hello!

In Numbers, is there a way to use a formula to convert time durations such as "7:30-1:45" into a decimal for hours (i.e. "6.25")?

I have been writing in the time frames that I am scheduled to work and would like to have a total at the end of each week in a column that converts the given time frames into the amount of hours I worked that week.

Is there any way to achieve this?

I have tried the "TIMEVALUE" option, as I feel that is the best option I could find, but it seems to only allow input of one specific hour.

Thank you for your time.

Posted on Apr 24, 2015 10:24 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,264 points

Apr 24, 2015 1:50 PM in response to Community User

Maybe something like this would work for you:

I assume your "7:30-01:45" is just Text in a cell. It is not a true Numbers 'Duration' data format.

You can split it into two separate strings that Numbers can interpret as times, then take the TIMEVALUE of each time, and derive the difference expressed in decimal hours.

The formula in B2 to extract the first time from the text:

=LEFT(A2,FIND("-",A2,1)−1)

The formula in C2 to extract the second time from the text:

=RIGHT(A2,LEN(A2)−FIND("-",A2,1))

The formula in D2:

=TIMEVALUE(B2)

The formula in E2:

=TIMEVALUE(C2)

The formula in F2:

=(E2−D2) x 24

You could combine some of the formulas to reduce the number of columns.

I used a 24-hour notation but am/pm also works if there are no spaces as shown:

SG

Reply

Answer 2

SGIII

Level 8

35,264 points

Apr 24, 2015 8:47 PM in response to Community User

Here it is all in one formula:

The formula in B2, copied down the column, is:

=24×(TIMEVALUE(RIGHT(A2,LEN(A2)−FIND("-",A2,1)))−TIMEVALUE(LEFT(A2,FIND("-",A2, 1)−1)))

SG

Reply