Numbers: Formula error when adding time with Duration cells

It is possible to add a Duration value to a Time value, as long as Numbers recognizes the various cell formats.

If you have a cell value like "1:30", it could be interpreted as half past 1 o'clock, or it could be 1 hour and 30 minutes, or it could be a 1:30 ratio, or even just a string of characters 1, colon, three, zero. That ambiguity is likely the cause of your problem.

By being explicit, or at least providing hints, Numbers will better react to your cells.

So instead of "1:30", enter "1:30 PM", or "1h 30m" - something that helps Numbers infer the data type

Hi again Bill,

I prefer explicit formats, rather than xx:yy. Then we humans (and Numbers ) can distinguish between Time of Day (Date & Time) and Duration (elapsed time). For example,

Formula in C2 is B2−A2

Regards,

Ian.

What if you enter 01:30 instead of 1:30?

Hi Bill,

What is the formula in cell B3? (I am guessing that the cell is B3 because your screen shot does not include column letters or row numbers).

Are you trying to add two times? In Numbers that is nonsense. Like adding Monday and Tuesday!

Or are you subtracting two times? That will give a Duration as the answer.

More information may lead to a solution.

Regards,

Ian.

Seem to have same problem, I use excel but have a few sheets in numbers from when I tried it, I gave up and stuck with excel.

check this sheet I made years ago, first 2 lines were made years ago, the new line adding time does not work, same error as you, all formatting is the same for all 3 lines?

[Edited by Moderator]

Yellowbox wrote:

Hi Bill,

What is the formula in cell B3? (I am guessing that the cell is B3 because your screen shot does not include column letters or row numbers).

Are you trying to add two times? In Numbers that is nonsense. Like adding Monday and Tuesday!

Or are you subtracting two times? That will give a Duration as the answer.

More information may lead to a solution.

Regards,

Ian.

Adding two times is perfectly sensible. My train leaves at 13:35 and the journey takes 6:27 so if I want to know what time it arrives I add the two times. It works in Excel and LibreOffice without having to distinguish between a time and a duration.