Entering a duration using colon format

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

SGIII wrote:

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.

I totally agree except that the last request about this mentioned wanting to do data entry on the "10-key" keypad, which is only numbers and a decimal point (and other math operator symbols). That was the rationale behind what I did.

Right, I saw the "10-key" keypad reference and puzzled over what it meant, saw your interpretation, and then thought "decimal" entry with a script to convert to "true" duration would be an efficient approach if one must use that kind of keypad for bulk data entry. Otherwise the built-in 1h12m or 1h12m30s entry notation to me seems tough to beat: it's concise, quick to type, and unambiguous.

SG

I am an airline pilot and keep flight logs spanning 10,000+ flights. My monthly schedule bids require about one hundred time entries into a spreadsheet as well. Most airlines track flight times as either HHMM or HH.MM, as do almost all logbook programs I've encountered. As a former accountant, I'm pretty good with a 10-key. As you could imagine, typing all entries with one hand without having to look at the keyboard, move your hand around, etc, is very important when entering tens of thousands of records (not to mention trying to transpose the airline's records of HH.MM on the fly to HHh MMm).

I understand your point about the zero-keystroke difference, but I never said I wanted to use ':' (which requires two strokes), I said I was only using that because of the Pages limitations (whereas I want to use '.'). So it's actually much larger difference than that. I can type HH.MM <enter> using six keystrokes using one hand, all quickly and efficiently without having to look. On the flip side, typing HHhMMm <enter> takes one additional keystroke, but requires either two hands and/or looking down constantly, which greatly slows the progress. Try switching from ten-key (right hand) to letters (left hand) back to ten-key (for enter) for each number, several hundred/thousand times. It's very confusing, and VERY slow! I was probably underestimating when I said 4x slower, as it's more likely 5x-6x slower due to the snowball effect.

I realize my case may be somewhat unique, at least outside my industry, which is why I turned to this forum. I am simply trying to recreate my custom-built web-based (PHP) solution in Apple Numbers to enhance my offline experience. I appreciate all your input. Don't mean to write a dissertation, just wanted to give some background as to why the Apple-built approach doesn't work for my needs. Thanks for your response!

Thanks for offering this solution. I was having trouble duplicating the custom format you offered up (exactly how to get the ##.##), but it's not really the solution I was seeking anyway. Everything in the airline world is tracked as HH.MM, and I've been reading/writing/adding that manually for the last 13+ years, so I don't want to start converting to HH:MM (I'm too old for that)! I won't be implementing your formatting, but your formula was very helpful. Thanks!

Great, this was along the lines of what I had already done, but your advice helped. I'm using my data entry column as H.MM or HH.MM, formatted as a number with 2 decimals (we have no need for seconds in the aviation world, thus the problem with Apple's one-size-fits-all approach). Then I'm using a second, hidden, column with your extremely helpful duration formula. I had come up with something else, but it required much more brute force, and yours is more elegant and all within a single cell. Excellent!

Thanks for the =DURATION(,,INT(A2÷100),MOD(A2,100)) formula. It is very short and sweet, though I'm still trying to figure out exactly what it's doing (I understood the other Left/Right/Find formulas fine, though they were complex).

When I enter 1212, this formula appropriately gives me 12:12.

However, when I enter 12.12, this formula results in 0:12 (when I want/expect 12:12). Once I work through your formula to understand what it's doing, I'll surely understand why it's not working as I would want :-)

In a nutshell, I'm wanting to enter (and display) HH.MM, though have Numbers interpret this as HH:MM for calculation purposes. Your Left/Right/Find solution I believe is likely what I'll end up sticking with, as it does seem to work as I need. I'll keep plugging away at this. Meanwhile THANK YOU for all your suggestions and thought!!

I agree, the XhXm thing makes great logical sense and is the "plain english" way of doing things, but it's just not the fast and efficient way of entering lots of data for my particular needs (it's much easier to type fast when you're merely duplicating what you see, rather than translating on-the-fly. Thanks for all your input, however. I APPRECIATE your time!!

Wow, thanks for the script. I'll definitely look into this, though 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"). I appreciate that your script will likely be faster if doing occasionally on a large chunk of numbers, and it will keep the data "true." Thanks!

Hiroto, interesting, I'm currently testing this out. Yeah, it took me a while to finally grasp the differences between date/time and duration in Numbers. Date/time format correctly interprets my entry of 1:12 as 1hr 12min, though my primary purpose for entering times is to add them, for which they need to be in Duration format.

I was previously using this formula to convert my date/time entries to numbers for purposes of addition, prior to converting back to durations:

(HOUR(A1)×60+MINUTE(A1))÷60

This seemed to work accurately for all my tests, but now I've replaced that with Badunit's formula:

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

Thanks for the suggestions, and for your time!

Thank you. This looks similar to the first solution I had implemented (partially), and I will check it out. Thanks!

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

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. As a dynamic page (this is not my actual logbook, just a temporary log for weekly/monthly pay calculations), I will have to be running the script quite often, whereas the hidden column of calculations makes everything happen in real-time, with no action necessary. That said, your script would definitely be the optimal solution on a full logbook with thousands of entries, due to speed. I had to convert my logbook from Excel to a database years ago due to the size/speed limitations of an enormous spreadsheet with thousands of calculations. Database is quick easy, and extremely flexible for things like logbook when data needs to be sliced/diced in myriad ways. But it is overkill for a simple spreadsheet like I was creating here. Thanks again for offering up the solution. I appreciate your time/effort!

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!