Adding hours and minutes

Were you able to try & see if some suggestions here may work?

• Create a custom cell format - Numbers Help for Mac:

http://help.apple.com/numbers/mac/3.2/#/tan090042718

The database may also yield other helpful items, if checked further.

Good luck & happy computing! 🙂

If the list is decimal hours, and you have the sum, then use that sum (as decimal hours) as an argument to the duration function.

The syntax for the duration function is:

DURATION(weeks,days,hours,minutes,seconds,milliseconds)

any field you do not want to use a zero.

In your case... let's assume that the sum in hours is in cell A5

you can get the duration in hours and minutes by:

1) using the duration function like this:

DURATION(0,0,A5,0,0,0)

then

2) select the cell where you placed the duration function and format as a duration using the cell formatter:

Hi Al,

The cells which hold your "long list of hours and minutes" must be formatted as Duration, or the duration values must be entered in a format that Numbers cannot misread as the time part of a Date and Time value.

Example:

All cells in column B (excluding B1, which is in a Header row, and is not included in the calculation) are formatted as Duration, with the units indicated as shown.

For the example, the values in this coumn are random, created by the formula below, entered into each of the cells in the column.

B2: =DURATION(,,RANDBETWEEN(0,12),RANDBETWEEN(0,59))

Filled down the rest of the column.

C2 and D2 each contain the formula shown above the table: =SUM(B)

Both C2 and D2 are formatted as duration, but to display as Hh Mm (C2) or H:MM (D2). Both are set, using the Inspector, to show only Hr and Mn values.

"I cannot work out what keystrokes I need to do to enter something like 4 hours and 30 minutes. Is it 4:30 or 04:30 or 04 30 or 4,30??"

4h 30m will always be interpreted as a duration value.

4:30 will be interpreted as a duration only if the cell's format has been set to Duration; otherwise, it will be interpreted as 'time of day.'

4,30 (or 4.30, depending on your region) will be interpreted as a numerical value (four decimal three zero, or four and 30 hundredths)

4 30 will be interpreted as text, due to the internal space in the string of characters.

Click the Format brush and select Cell to set the format of a cell (or group of cells).

Regards,

Barry

Hong Kong Al wrote:

I cannot work out what keystrokes I need to do to enter something like 4 hours and 30 minutes. Is it 4:30 or 04:30 or 04 30 or 4,30??

Hi Al,

To input a Duration value just type 4h30m. (If you want minutes and seconds you would type 4m30s). This does not require preformatting the cells. When you type that Numbers immediately knows you mean to enter a Duration.

As with any values entered in Numbers you can then adjust how these values are displayed, for example in the colon format if you wish.

SG

Hong Kong Al wrote:

Managed to to what I want to do now. It was simply a case of knowing how to enter the data.

4 hours 30 minutes needs to be input as "4 h space 3 0 m"

Here I just type 4h30m. No need to type spaces in my U.S. region settings. (I tried changing the region setting to HK and 4h30m worked there too).

That's for inputting. After the duration is input, it is immediately displayed as 4h 30m, unless I change to colon format, in which case it displays as 4:30.

SG

HI Al,

"Then I enter 4:30 (numeral 4 followed by colon followed by numeral 3 followed by numeral zero) in B1. I expect to see 4h 30m but instead I see 0h 5m.

So I think (hope) my question is very simple - what keystrokes do I need to do to enter 4 hours and 30 minutes?

Sorry if I I am being a bit thick but I am desperately trying to become a fan of Numbers after having used Excel for years and get a bit frustrated when I am obviously doing something wrong."

1. The format pane determines only the how the value in a cell is displayed. Apparently Numbers is interpreting your entry ( 4:30 ) as four minutes and 30 seconds, the displaying that value rounded to the nearest minute to fit the format you have set. If you're curious, you could confirm this by entering 4:30:00

I was curious, and tried it in Numbers 2, where in cells formatted as Duration and displayed units set to Hr and Mn, either entry ( 4:30 or 4:30:00 ) was accepted as meaning 4h 30m, and produced the correct result when included in a sum. Perhaps behaviour was changed in Numbers 3.

2 As I responded to your earlier version of this question: "I cannot work out what keystrokes I need to do to enter something like 4 hours and 30 minutes. Is it 4:30 or 04:30 or 04 30 or 4,30??"

4h 30m will always be interpreted as a duration value.

3. Understood. Most spreadsheet applications are similar, but all have some differences. It can be difficult to adjust to those differences. I get frustrated trying to do things I find simple on a Mac that don't work in the same "intuitive" manner in a similar Windows application. The frustration is lowered sometimes when I realize what I'm thinking of as "intuitive" would be better described as "the way I've always done it," then step back and take a fresh look.

Regards,

Barry

Hi Al,

In Numbers 3, leave the cells formatted as Automatic.

As SG says, simply enter 4h30m (no spaces required)

Now press enter to confirm (Numbers puts a space)

Now click in B2 to see the Data Format (and click on Custom Units in the Format Panel)

As SG says, you can now change the Style to colon (4:30) and it is still a Duration, not a Time & Date

Regards,

Ian.

If you enter 4:30 it is taken as 4 minutes 30 seconds, even if you have the cell formatted as hh:mm. You would think that if the cell was formatted as hh:mm, the result of 4:30 would be hours and minutes but it is not, it is still minutes and seconds. To get hours and minutes you can do what has been suggested already, entering it as 4h30m, or you can enter it as 4:30:0 which is 4 hr 30 min 0 sec. Entering as 4h30m does not require pre-formatting the cell as a duration (unless you want to see the hh:mm format). Entering it as 4:30:0 requires pre-formatting the cell to duration, otherwise it will take it as a date-time value of todays date and that time of day. I recommend 4h30m. It is easier, less keystrokes, and more robust. It will always be taken as a duration, never as a date-time value.