How do I get numbers to provide a sum of times where total is greater than 24 hours?

Hi Lessbarb,

Please post a screen shot of the minutes data.

Twenty minutes should be 20m

0:20 is the Time portion of a Date & Time format and is meaningless to add.

Changing the cell format to Duration does not "stick"; Numbers (correctly) reverts it to Date & Time.

Regards,

Ian

Hi Lessbarb,

Numbers has interpreted the original data as Date & Time. For example, 0:17 appears as 07/07/2017 (the date when I entered the data, 12:17:00 am (17 minutes past midnight).

No problem. We can pull components from the original data (Column A, let's pretend they are mm:ss) and rebuild them into Duration format. Here are the steps. They could be combined into a single, complex formula, or simply hide columns B, C and D when all is working.

Column A contains the original data.

Formula in B2 (and Fill Down)

=LEN(A2)

Formula in C2 (and Fill Down)

=LEFT(A2,B2−3)

Formula in D2 (and Fill Down)

=RIGHT(A2,2)

Formula in E2 (and Fill Down)

=DURATION(,,,C2,D2)

The commas tell Numbers that there are no weeks, days or hours in the Duration

Column E is formatted as

Regards,

Ian.

PT,

Numbers calculates elapsed time as the difference between End Date/Time and Start Date/Time. This difference is in Data Type Duration. If your shift begins at January 4, 2013 10:00 hours ends at January 5, 2013 12:00 hours, the Duration will be 26h.

Be aware that if you only enter Time, the Date will be assumed to be the Date on which you made the entry, so both times will be entered as the same day.

Jerry

Hi PT,

As Jerry says, you should be adding durations, not times. What would adding 1 o'clock to 2 o'clock mean?

Regards,

Ian.

If you want to do this the Excel way, you will have to use Excel.

Jerry

If you try to add times, you will get an error, not an incorrect result.

If you add the durations 12:35:00 and 12:45:00 you will get 1day 1 hr and 20 min which is 1:1:20:00 or 25:20:00. I don't know why you are not seeing the day portion of that amount.

If you did enter all these durations at Date & Time, you can convert them with =DURATION(,TIMEVALUE(A)) where the "A" in this formula is the column with Date & Time values. Then format the cells so they only show hours, minutes, and seconds

What version of Numbers are you using?

I can get 12:35:00 + 12:45:00 to equal 1:20:00 only if 12:35:00 is a Date & Time and 12:45:00 is a duration (or vice versa). The result is 1:20 on the next day (another Date & Time value).

So, what I'm thinking is you have one Date & Time value on your list and the rest are durations.

ptfjjj wrote:

I already reentered the numbers as durations, so conversion won't be necessary. Thanks, anyway.

That's what I assumed in my last post except I bet one of them is a Date & Time. That's the only way for you to be getting the answer you are getting.

Select all the cells you are trying to add. Set the cell format to Duration. Click somewhere else then select them all again. I bet it now says "Multiple" for the cell format. Go through them one by one until you find one that is not Duration. It will probably be "automatic" but will have the formatting info for a Date & Time cell. Or maybe a quicker way is to set them all to Duration with 0w style (so they are formatted like 12h 35m) and look for the one that didn't change to that format.

ptfjjj wrote:

version is 3.0.1, but I already reentered the numbers as durations, so conversion won't be necessary. Thanks, anyway.

I'm not sure that any of us has a good handle on how Numbers 3 handles Dates, Times and Durations, but we can say with some confidence that it is different from Version 2 and not always user friendly. There are indications that if you want to enter a Duration and do it in a form that appears as a Time, it will be taken as a Time. The sure way to enter a Duration is to use the format 1h 30m. If the cell you are entering this duration into is formatted as hh:mm, your entry will convert to that display, or in this case 1:30.

Jerry

I think you have this figured out now, but just in case...

"If I wish to add 12:35:00, 12 hours and 35 minutes to 12:45:00, 12 hours and 45 minutes, I want have Numbers sum them up to be 25:20:00, 25 hours and 20 minutes, but the result that Numbers gives is 01:20:00. I need to figure out how to get Numbers to keep adding the total hours, no matter how many there are."

"12 hours and 35 minutes" is a duration. "12 hours and 45 minutes" is also a duration. If you add them, the result is also a duration—"24 hours and 80 minutes", or more conventionally, "25 hours and 20 minutes".

"12:35:00" could be either a time (12:25 pm on a specific date), or a duration. if you do not specify which, Numbers will default to cosidering it a time, and wiii record it as a full Date and Time value, assuming the date part to be the date the entry was made.

When you then enter 12:45:00 and attempt to add it to the previous time, Numbers needs to interpret that entry in a manner that makes sense in the context.

Adding a time of day to another time of day does not make sense ( 12:35 PM + 12:4 PM = ? —there's no value that can replace the ? and make a true arithmetic sentence). But adding a duration to a time of day does give a meaningful result: 12:35 pm + 12 hours and 45 minutes = 1:20 am (the next calendar day). If the result cell is formatted to show only the time part of the Date and Time value, it will display 1:20 am. If it is formatted to show both date and time parts, it will display the date one day later than the date contained in the cell displaying 12:35 pm and 1:20 am.

In your case, you are not calculating flight durations from start and end times for each flight, but are entering them directly. Each flight duration needs to been entered as a duration. When summed for the year, the result will be a duration, which can be formatted to display only hours and minutes. As can be seen in the table below, his format will accomodate as many hours as there are in a year.

Regards,

Barry