Entering a duration using colon format

L,

It seems that you can enter durations using the colon separator format only if you use HH:MM:SS. Numbers assumes that the last Using your example, you would have to enter:

3:15:00.

Jerry

If you format the cells as Duration prior to entering the duration, you can use colon format to enter a duration but you have to enter it as 3:15:0 for it to be hours and minutes. If you do not pre-format the cells as duration, if you enter 3:15 or 3:15:0 it will make it a Date & Time of today at 3:15AM.

Numbers seems to default to a Date & Time format unless the number cannot be a time. If you enter 195:0, you will get 3h 15m. I found that if you enter 0:3:15:0 in a cell formatted as "automatic", you will also get a duration of 3h 15m.

I don't know if it serves your purpose, but you can cheat and use a custom MM:SS format:

If you're simply creating a list of durations it should do the trick without having to repeatedly type in the last ":00"

Regardless of which units I choose, 3:15 becomes 3 minutes 15 seconds

Works for me if I set the style to 00:00

I do get leading zeroes before the minutes though:

As I said, my answer was a cheat rather than a solution.

I have the same problem. I am trying to add the following durations:

A + B = C

1:12 + 2:24 = 3:36 (in English, 1hr 12min + 2hr 24min). I'm trying to convert the PHP program I created to a Numbers spreadsheet for offline ease of use.

If A and B are formatted as durations, the only way to enter properly is to type HH:MM:SS, which is quite painful when entering thousands of entries, especially when I have no need for seconds (this is already painful, as I actually write durations in this format: HH.MM). The C is accurate sum of the durations, though it is a bunch of unwanted typing.

If A and B are formatted as times, however, I can enter 1:12 and it formats properly. However, SUM does not work for multiple time values. So it appears I get the formatting I want when using date/time, though I can only perform the calculation when using duration.

Regardless of what I select in the format drop down box (I have HH:MM selected), this requires the input of :SS in order to format properly (otherwise typing in 1:12 reformats to 0:01, which is not as intended).

Thanks! It appears that Numbers' expectation to also use seconds in the duration cells is what's causing my (and OP's) heartaches. As a workaround, maybe I could just type 1.12+2.24=3.36 (as I have in my PHP program, which treats decimal and colon as the same thing). I don't know how to "explode" the integer and decimal portions of the number though. Any ideas? Thanks!

Hi Philip,

Why not just enter as 1h12m and 2h24m? You can change the display format to suit your needs.

SG

Because, as I mentioned above, trying to enter 10,647 entries on a 10-key such as "12h 47m" is nowhere near as fast or productive as "12.47". It increases the time to enter by about 4x! Thanks, though.

Hello

Option 1. Let Apple fix Numbers v3 so that it properly honours duration format that user defined when inputting duration value.

Option 2. Use Numbers v2, which behaves properly in this regard.

Option 3. Format the input columns (A, B) as date-time value in format HH:MM and output column (C) as duration value in format HH:MM, and put the following formula in C:

=DURATION(,TIMEVALUE(A)+TIMEVALUE(B))

E.g.,

Table 1 A2 2015-11-19 01:12:00 B2 2015-11-19 02:24:00 C2 =DURATION(,TIMEVALUE(A2)+TIMEVALUE(B2))

* Table is built with Numbers v2.

Regards,

H

Philip Sterling wrote:

Because, as I mentioned above, trying to enter 10,647 entries on a 10-key such as "12h 47m" is nowhere near as fast or productive as "12.47". It increases the time to enter by about 4x! Thanks, though.

Hi Philip,

Not sure where you get that 4x difference. Entering 12h47m vs 12:47 is a zero keystroke difference (six keystrokes either way) because on my keyboard you've got to hit shift if you want a :

Numbers 3 does "honor" the duration format you've chosen when you enter the duration values the way it's designed to accept them. If you've chosen the : format for the cells (as shown in my screenshot) then when you enter 12h47m the value is displayed automatically as 12:47.

If you want you can give feedback to Apple via Numbers > Provide Numbers Feedback. However I suspect there may be a lot of users like me who appreciate the way entry of durations is handled now, which I find efficient on the Mac (and also in iOS).

SG

Philip Sterling wrote:

Because, as I mentioned above, trying to enter 10,647 entries on a 10-key such as "12h 47m" is nowhere near as fast or productive as "12.47". It increases the time to enter by about 4x! Thanks, though.

Numbers will interpret a decimal entry as decimal hours. 12.47 will be 12 h 28m 12 s.

If you want to enter durations as a decimal hh.mm, you can pre-format all those cells to be text or a number with 2 fixed decimals (to ensure it always has a decimal point and minutes even if the minutes are 00). In another column you can use formulas to convert that text into a duration.

If A2 has your "durations in hh.mm format (no .ss) then

B2 = DURATION(,,LEFT(A2,FIND(".",A2)−1),RIGHT(A2,LEN(A2)−FIND(".",A2)))

Here is another idea. Saves keystrokes too.

For your column of "duration" entries, create a custom format"

Data entry will be just the hours and minutes, no colons no decimal points. You must always type two digits for the minutes, even if they are 00. It will automatically format to hh:mm format. If your type 1247 the result will look like 12:47. If you type 12000 the result will be 120:00. If you type 47 it will be 00:47. Not that it matters here but just so you are aware, the actual underlying number in the cell will be exactly what you typed, 1247 it will look on the screen as 12:47 but the number in the cell will still be 1247.

In another column, use a formula like in my post above except now we are looking for the colon instead of a decimal point

DURATION(,,LEFT(A2,FIND(":",A2)−1),RIGHT(A2,LEN(A2)−FIND(":",A2)))

You can hide this column of formulas after you get everything set up. All your users will see is the column of fake "durations".

An alternate formula that works on the number in the cell, not the text representation of it on the screen, would be

=DURATION(,,INT(A2÷100),MOD(A2,100))

This formula is most likely much faster than the text one. That could matter if you have a lot of them. It is also more robust because it uses the actual value in the cell not how it is displayed on the screen. And it is shorter and more clearly understandable.