Zero as a Duration

Response for this kind of question is available in *_Numbers User Guide_* or in *_Formulas and Functions User Guide_*

Select the range of cells in which you must have durations values
then Inspector > Cells > Duration

Yvan KOENIG (VALLAURIS, France) samedi 4 septembre 2010 17:51:57

My G5 running 10.5.8 died so I can't test the behavior under 10.5.8.

Under 10.6.4, if C2 and D2 contain the same duration value, the formula
=D2-C2 return 0h
so, I don't understand where is the problem.

0 inserted in a cell is a duration if the cell is defined this way.
Calculations upon durations return durations.

The unique case where it drops the duration format is when we make calculations with items of different formats.
if a cell multiply a currency value and the difference of two durations, the result is a pure number because the application has no means to guess if we want a currency result (which is the logical behavior for a human being) or if we want a duration result (which would be ridiculous).
It would be fine if Numbers was always behaving this way.
Try :

B2 : 10$
C2 : 12€
D2: 11£
On a system whose default currency is €,
=B2*C2 as bad as =C2*B2 will return 120€
=B2*D2 as bad as =D2*B2 will return 110€
=C2*D2 as bad as =D2*C2 will return 132€
On a system whose default currency is $, I guess that they will return 120$, 110$, 132$

All these results are ridiculous.

It would be fine if the engineers where fair enough to introduce consistency tests :

currency * currency is ridiculous
weight * weight is ridiculous
length * length is valid (it's a surface)
length * length * length is valid ( it's a volume)
currency/currency is valid it it doesn't apply a currency to the result but at this time, it apply one 😟

When my ten years old grand-son got an euro value when he divided two euros ones, he came saying "Papy, the machine became fool".

Yvan KOENIG (VALLAURIS, France) lundi 6 septembre 2010 16:35:40

He is correct (at least on my Mac) that if you format two cells as date & time but put no values in them then subtract one from the other, the result is zero, not a duration of 0h, even with the destination cell pre-formatted as a duration. If the blank cells are formatted as "automatic", the result is also a 0, not 0h.

So, if the original poster is doing calculations on blank cells, it is possible that the result will not be a duration. Two solutions I can think of:

1) Pre-fill the blank cells with whatever it takes to make the result a duration.
or
2) Use a formula like =IF(ISERROR(DUR2HOURS(D3-E3)),DURATION(0),D3-E3)