NUMBERS - Show hours with a decimal point

I have a column that lists durations, and has a sum at the bottom. I need to convert the cell with the sum to hours with 3 decimal points.

F7 2h 15m
F8 1h 10m
F9 3h 5m
F10 6h 30m (SUM F7:F9)

I need F11 to be 6.500

Every time I try to do this - it automatically changes the cell to duration format - and even if I change it to hours only - it rounds it up to 7h.

Any help is appreciated.

Numbers - syntax error in sum cell when blank

I am creating a time sheet - I want to add two duration columns (Total Hours Excluding Travel and Travel/Mileage). If there is a duration in at least one of the columns - it works fine. However - when both of the columns are blank - it has a syntax error.

It is important to note - that the Total Hours Excluding Travel Column has this formula: IF((D7−C7)−E7=0,"",((D7−C7)−E7))
However - if I remove that formula - I get the same syntax error if columns C through E are blank (Time Started, Time Finished & Lunch Break).

Please help!

Welcome to our forums.

Joined both of your posts together as they can be answered together.

It is important to note - that the Total Hours Excluding Travel Column has this formula: IF((D7−C7)−E7=0,"",((D7−C7)−E7))
However - if I remove that formula - I get the same syntax error if columns C through E are blank (Time Started, Time Finished & Lunch Break).

Click to expand...

Welcome to the forum.

You cut them off in the image, but I'll assume the columns are A through I in the image.

Can you tell us what that quote means? Do you mean that if the formula is not in the Total Hours Excluding Travel Column then you get an error in the formula for the sum of that plus Column G in Column H? Or is something else going on. It's not clear from your description. Part of the problem may be that by putting a null (the "") in the cell if the sum comes to 0, you are putting a text symbol in a time column or numeric column, which then creates a syntax error in the other formula. You might try changing the "" to a 0, and see if that works. If the 0 is unacceptable (even if accurate) then you may have to do some extensive work to nest IF statements to handle the 0 condition.

Assuming that this syntax is like Excel, which it appears to be, then your formula is syntactically ambiguous. Try this:

If(( D7 - ( C7 + E7)) <= 0,"", ( D7 - ( C7 + E7))

Group like this, Works in Excel at least.

As for your first problem try formatting the cells containing totals as a 2 decimal number instead of time and use a formula like: 24*sum(F7:F9)

Not sure this will work but in Excel it does something indicating it probably will. The exact time format you use is not available to me in Excel so can't say for sure.