Converting time to decimal time in numbers for mac

Hi tye49 and welcome to Apple Discussions!

No need for complicated DUR2 formulae, MINUTE or anything like that: just divide the minutes figures by 60 and then format the result to one decimal place:

My minutes are in B2. My formula in C2 is B2÷60:

My formatting for C2 is Number, with 1 decimal place:

Changing the minutes in B1 changes the hour figure to the same given in your table:

etc etc.

Cheers,

H

Are you looking to do something like this?

Formula in C2, filled down:

=DUR2MINUTES(B2−A2)

Formula in D2, filled down:

=C2/60

Numbers has a whole family of functions that convert durations to decimal. In your menu at Help > Formulas and Functions Help you can find descriptions and examples:

SG

HD's solution assumes you are recording the number of minutes (a number), a reasonable assumption given the example in your initial post.

But the example in your second post shows you are recording the start and end times (both Date and Time values) then subtracting one from the other, giving a result that is a Duration (formatted to express the result in minutes).

You then divid that Duration by a number (60).

HD's solution, as you've found, will not work with Duration values.

Your original choice of DUR2HOURS is the correct one for the calculations you are using. DUR2HOURS converts a Duration value to a number representing the number of hours in that Duration.

Here's a screen shot of a reproduction of your second example, exhibiting the issue (too many digits) mentioned in your initial post.

Columns A, B and C are the same as your example. A and B contain entered data. C2 contains the formula below, filled down to the rest of the rows in column C.

C2: B2-A2

Column D contains the same formula as column C, wrapped in DUR2HOURS:

D2: DUR2HOURS(B2−A2)

As you've noted,for Date & Time values not giving a result tht is a multiple of 6m, DUR2HOURS will returns a value with several places after the decimal.

That issue is resolved in column E by wrapping the formula in ROUND, and specifying the precision to one place after the decimal:

E2: ROUND(DUR2HOURS(B2−A2),1)

The formulas in C2, D2 and E2 are independent of each other. If you want to go directly to the results in column E, place that formula in C2, fill down from there, and eliminate columns D and E..

One further note:

The rows of Column C, where there are no entries in columns A and B return a result of 0 (a number) as Numbers interprets both empty cells as containing the numeric value zero, and returns the result of 0-0=0.

In columns D and E, the result of this subtraction, a number, causes an error in DUR2HOURS, which is expecting a Duration value, not a Number.

Thre are two ways of avoiding the error message—add an error trap by wrapping the formula in IFERROR, or preventing Numbers from carrying out the calculation by requiring entries in both A and B before permitting Numbers todo the calculation.

IFERROR(formula,"") places a null string in the cell if the formula returns any error message.

IF(OR(LEN(A2)<1,LEN(B2)<1),"0m",formula) presents a Duration value (zero minutes) if either A2 or B2 does not contain an entry.

Regards,

Barry