Entering a duration using colon format

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!

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

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.

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!

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!

Hello

Yes, you're right that you need auxiliary column to sum variable number of rows of time value in h.mm notation. And for simplicity and maintainability of formulae, I think auxiliary column approach would be better even for fixed number of rows of such time value.

Here's my revision using auxiliary columns (B and D) to convert h.mm value to minutes, which are summed at end to calculate the total duration value in A10 and C10. (Basically the same as your first solution except for the conversion formula)

Table 1 A1 scheduled A2 3.1 A3 1.12 A4 2.14 A5 3.15 A6 5.16 A7 A8 A9 A10 =DURATION(,SUM(B)/1440) B1 m B2 =SIGN(A2)*(INT(ABS(A2))*60+MOD(ABS(A2),1)*100) B3 =SIGN(A3)*(INT(ABS(A3))*60+MOD(ABS(A3),1)*100) B4 =SIGN(A4)*(INT(ABS(A4))*60+MOD(ABS(A4),1)*100) B5 =SIGN(A5)*(INT(ABS(A5))*60+MOD(ABS(A5),1)*100) B6 =SIGN(A6)*(INT(ABS(A6))*60+MOD(ABS(A6),1)*100) B7 =SIGN(A7)*(INT(ABS(A7))*60+MOD(ABS(A7),1)*100) B8 =SIGN(A8)*(INT(ABS(A8))*60+MOD(ABS(A8),1)*100) B9 =SIGN(A9)*(INT(ABS(A9))*60+MOD(ABS(A9),1)*100) B10 C1 actual C2 3.29 C3 1.02 C4 3.04 C5 5.06 C6 7.08 C7 C8 C9 C10 =DURATION(,SUM(D)/1440) D1 m D2 =SIGN(C2)*(INT(ABS(C2))*60+MOD(ABS(C2),1)*100) D3 =SIGN(C3)*(INT(ABS(C3))*60+MOD(ABS(C3),1)*100) D4 =SIGN(C4)*(INT(ABS(C4))*60+MOD(ABS(C4),1)*100) D5 =SIGN(C5)*(INT(ABS(C5))*60+MOD(ABS(C5),1)*100) D6 =SIGN(C6)*(INT(ABS(C6))*60+MOD(ABS(C6),1)*100) D7 =SIGN(C7)*(INT(ABS(C7))*60+MOD(ABS(C7),1)*100) D8 =SIGN(C8)*(INT(ABS(C8))*60+MOD(ABS(C8),1)*100) D9 =SIGN(C9)*(INT(ABS(C9))*60+MOD(ABS(C9),1)*100) D10

All the best,

H

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