Adding time duration in Numbers

Hi David,

The error message indicates you have at least two values in your list that Numbers is interpreting as (the time part of) Date and Time values. When entering durations directly, it's safest to do so using the '2h 3m' notation. Entering that value using 2:03 is ambiguous in two dimensions: Numbers may interpret that entry as 2:03 AM, and record it as a date (today, the day the entry is made) and time (02:03:00) rather that as a duration, OR Numbers may recognize it as a duration value, but err in assigning the correct scale to the ambiguous notation. 2:03 could mean 2 minutes and 3 seconds or 2 hours and 3 minutes.

Regards,

Barry

Hi David,

To save retyping all the Date & Time values, you can rescue them.

For example, 1:00 entered in A2 will show as Actual at the bottom left of the window as 22/05/2018 (the date when I entered 1:00 here in Australia) and 1:00:00 am (a Time, not a Duration).

Formula in B2 =HOUR(A2)

Formula in C2 =MINUTE(A2)

Following Roger's idea of converting hours to minutes then adding, formula in D2 =B2×60+C2

(Thanks, Roger!)

Formula in E2 =DURATION(,,,D2,seconds)

The leading commas tell Numbers to ignore Weeks, Days and Hours when converting Minutes to a Duration.

Column E is set by Format Panel > Cell > Data Format > Duration > Custom Units

Regards,

Ian.

As it happens I created a Numbers spreadsheet for adding times some time back, and you are welcome to use it or mine it for the formulae. It's in the older Numbers format but should open in the more recent version.

http://rfwilmut.net/share/Add_times.zip

It does separate the hours, minutes and seconds into separate columns - I don't know whether that will do for you but I'm not sure if you can do it as a single column.

Hi David,

Please post a screen shot of your data (command shift 4 and drag, then use the camera icon in a reply to post it here).

Or, select some cells and Copy, then Paste into a reply.

It will help us to help you if you tell us your version of Numbers (Menu > Numbers > About Numbers).

Regards,

Ian.

Hi Roger,

Thanks for the link. The download opened in Numbers 5.0.1 with a Sheet name "Imported AppleWorks Data".

Just out of interest, what app and what version created the download document?

Regards,

Ian.

The spreadsheet was originally created in AppleWorks, probably v6, then imported into Numbers 2.3 which is what I still use. Hence the 'Imported AppleWorks Data' label, which doesn't show as prominently in my version. Row D in the original version (evidently you've altered it) is covered by a white graphic to hide the internal working, and there are a lot more rows.

Hi Roger,

Row D in the original version (evidently you've altered it) is covered by a white graphic to hide the internal working

Yes, I did some clicking around and found the white graphic. I dragged it to the right 👿.

Exported from Numbers 5.0.1 to Numbers 2.3 (Numbers '09)

Regards,

Ian.