Entering a duration using colon format

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.

Philip Sterling wrote:

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

A + B = C

1:12 + 2:24 = 3:36

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

Simply entering as 1h12m and 2h24m seems to me by far the simplest and most efficient way (note that you do not need to worry about seconds). There are big advantages to immediately having "true" durations in the cells rather than trying to manipulate numbers or date-time strings.

If you have a strong preference for avoiding the duration syntax for entry and need to enter as 1.12 and 2.24 then here is an easy way to get "true" durations in your cells.

1. Format the cells in that column as text.

2. Enter the "durations" as 1.12 and 2.24 etc. down the column.

3. Copy-paste the script below into Script Editor (in Applications > Utilities).

4. Select the cells with the "durations." (in example B2:B5)

5. Click the triangle "run" button in Script Editor.

Now the cells are filled with "true" durations that sum properly. You can format them to fit your needs, with or without seconds, etc.

SG

-- enter "durations" as 1.12 2.24 etc in cells formatted as text

-- select cells, run; script converts to "true" hh:mm duration

tell application "Numbers"

tell front document

tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with aCell in selRng's cells

tell aCell

if format is text then

set v to value as text

try

set value to my decToDur(v)

set format to duration

end try

end if

end tell

end repeat

end tell

end tell

end tell

end tell

to decToDur(decStr)

set AppleScript'stext item delimiters to "."

set theItems to decStr'stext items

set AppleScript'stext item delimiters to ""

return theItems's item 1 & "h" & theItems's item 2 & "m"

end decToDur

SOLVED

Problem:

Add a list of times in Column A in H.MM or HH.MM format, and make the duration usable to the remainder of the spreadsheet for time calculations.

My OLD solution:

Column A cells format: Date & Time (HH:MM format, military style)

Column A cells: H:MM

Column B cells format: Number (2 dec points)

Column B cells: (HOUR(A2)×60+MINUTE(A2))÷60

Column A Total format: Duration (hr:min custom units)

Column A Total: DURATION(0,0,SUM(B2:Bx),minutes,seconds,milliseconds)

So the old solution, with the help of a hidden column B, correctly adds a list of times input in HH.MM format and outputs the total in HH:MM duration, usable for calculations by the remainder of the spreadsheet.

My NEW solution:

Column A cells format: Number (2 dec points)

Column A cells: H.MM

Column B cells format: Duration (hr:min custom units)

Column B cells: IF(ISBLANK(A2),0,DURATION(,,LEFT(A2,FIND(".",A2)−1),RIGHT(A2,LEN(A2)−FIND(".",A 2))))

Column A Total format: Duration (hr:min custom units)

Column A Total: SUM(B2:Bx)

The new solution, with the help of a hidden column B, also correct adds a list of times input in HH.MM format and outputs the total in HH:MM duration, usable for calculations by the remainder of the spreadsheet, with the following differences:

1. The initial data can be put in using industry-standard decimal rather than colon, which means I can use 10-key exclusively for data entry.

2. The times are now converted to durations sooner in the process (in the B “helper column”), meaning the final column A total is simple sum of durations. This improves error-checking (so I can immediately see if column B matches column A, rather than mentally converting HH:MM to decimal hours (only slightly beneficial, as you could imagine I’ve got decades of practice doing this all manually, as 6 mins = 1/10 hr) :-)

3. By not having to input times formatted as date & time, I see myself avoiding potential unknown issues (since date/time & durations look the same on the surface, but don’t always play well together).

Both solutions solve the original problem of having to use a mishmash of formatting (date/time & duration) to handle what I know are all durations (but the computer doesn’t), and prevents me from having to type HH:MM:00 for every entry in a duration column (IMO, Apple should accept data entry in the same format specified for data format display). Best of all, by using a single hidden column for conversion, it allows me to enter times in H.MM format and correctly sums them in the HH:MM duration format, usable for calculations in the remainder of the spreadsheet. A small enhancement would be the ability to display-format durations as H.MM, though I know that’ll likely never happen, as it rightly could become confusing for some (i.e. does 4.50 mean 4:30 or 4:50? In the travel industry, we read 4.50 as 4:50, but I realize others might read it as 4 1/2 hours.).

THANK YOU to BADUNIT and HIROTO for the extremely helpful formulas!

THANK YOU to SGIII for the alternative script, even though I chose not to use.

I’m not able to mark any of these as OP's SOLUTION (I guess since I’m not the original thread owner, and my needs were similar but slightly different), but you have all helped me tremendously. THANKS!

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

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

I do get leading zeroes before the minutes though:

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!

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".

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

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.

Addenda.

Option 4. Use hhmm notation to input time. Like this.

Table 1 A1 A [hhmm] A2 112 B1 B [hhmm] B2 224 C1 A+B [hh:mm] C2 =DURATION(, (SIGN(A2)*(INT(ABS(A2)/100)*60+MOD(ABS(A2),100)) +SIGN(B2)*(INT(ABS(B2)/100)*60+MOD(ABS(B2),100)))/1440)

Option 5. Use hh.mm notation to input time. Like this.

Table 1 A1 A [hh.mm] A2 1.12 B1 B [hh.mm] B2 2.24 C1 A+B [hh:mm] C2 =DURATION(, (SIGN(A2)*(INT(ABS(A2))*60+MOD(ABS(A2),1)*100) +SIGN(B2)*(INT(ABS(B2))*60+MOD(ABS(B2),1)*100))/1440)

* These methods can handle negative time value as well. (Previous Option 3 cannot handle negative time)

Regards,

H

Philip Sterling wrote:

I sense it's a little too much legwork required every time I want to add data (my understanding is that I have to run the script each and every time I want to enter data, whereas the other method works "on the fly").

Hi Philip,

Not sure what you mean by "sensing" there is "legwork." Have you tried it? Once the script is written (which it is) there isn't much to it.

A script is typically installed in the menu (it's easy), or even attached to a keyboard shortcut if you like.

So you enter your various times ("durations") on your "10-key" (I assume that's a numerical keypad with the . but no : or h or m).

You enter the "durations" as as h.mm or hh.mm (first having formatted the cells as text).

Then you select the cells and make a quick menu choice to run the script, which converts all your entries to "true" durations.

That's it. If you have a ready made script (which you do) it involves far less legwork than setting up and maintaining extra columns with complicated formulas. And because you are then dealing with true durations, you don't have to worry about modular math and such.

SG

UPDATE:

I've now updated my solution to a tweaked version of that provided by Hiroto, as it allows me to eliminate the hidden column. I don't think this solution would work (the way I have it implemented) on a table in which I need a dynamic number of rows (my formula is static, thus it breaks when new rows are added). I am adding values vertically rather than horizontally as shown by Hiroto. However, as the relevant sections of spreadsheet in which I need this solution have a set quantity of rows, I figure it's worth the trade-off to eliminate the hidden calculations column.

In sections where I am needing a dynamic quantity of rows, Badunit's solution (which I used in the above post) is ideal. Thanks to all! Here's what my newest solution entails:

DURATION(,(


SIGN(A3)×(INT(ABS(A3))×60+MOD(ABS(A3),1)×100)


+SIGN(A4)×(INT(ABS(A4))×60+MOD(ABS(A4),1)×100)


+SIGN(A5)×(INT(ABS(A5))×60+MOD(ABS(A5),1)×100)


+SIGN(A6)×(INT(ABS(A6))×60+MOD(ABS(A6),1)×100)


+SIGN(A7)×(INT(ABS(A7))×60+MOD(ABS(A7),1)×100)


)÷1440)

And here is a screenshot of the relevant section...

Philip Sterling wrote:

Again, thanks for the script. I created it and ran it, and it worked well. The issue for me is it takes active effort to run the script every time I want to convert the data.

Hi Philip,

Glad to hear you find that the script works, and that you found another solution that works for you.

Just so later readers possibly suffering from "scriptophobia" don't misinterpret🙂: the "active effort" involved with this script is to select multiple cells and make a menu choice, in other words exactly the kind of thing we all do all the time when we use Numbers.

This not done after each entry. That would be too cumbersome. It's done after a batch of entries, and only when using a special keyboard makes it inconvenient to use the native Numbers syntax for entering (to be distinguished from displaying) durations. All this script does is go through each selected cell and change, say, 1:12 to 1h12m so Numbers recognizes it as a duration. For the user, this active effort takes perhaps a second or so.

Still recommend that the vast majority of users on regular keyboards just use the native Numbers duration input syntax, e.g. 1h12m . It's concise, easily typed, unambiguous, and does not involve scripts or inserting extra columns that have to be maintained with complicated formulas that can go wrong. The value will then display immediately in : format if that is how the cell is formatted.

And, interestingly, if the user by mistake still has the cell formatted as Text, Numbers is still in some cases smart enough to do the duration math.

Apple has done a good job on this in Numbers 3. Might as well take advantage of the convenience.

SG

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"