Adding a column of hours and minutes

Hi t'

You haven't shown the result you are getting in the Footer row of column D, but I suspect it is an error triangle.

With the data shown, your table should look something like this:

The formula in D10 is =SUM(D)

The error message causing the red triangle is: "Values being summed by SUM must be either all durations or all numbers, unless one value is a date."

The first two values in column D of my example (and the first 26 in column D of your table) are Durations. The rest (all zeroes) are numbers.

The fix is to revise the formula in the rest of column D so that it always produces a Duration value.

Your existing formula in D2 (using cell addresses rather than the labels in the header row and header column):

=SUM(C2-B2)

SUM() is redundant here, and may be dropped.

The formula needs editing to produce a Duration (0m or 0h) until Date/Time values have been entered in both columns.

Revised formula for D2:

=IF(OR(LEN(B)<1,LEN(C)<1),DURATION(,,,0),C−B)

Fill the formula down to all rows except the footer row of column D.

The IF part tests cells on this row of columns B and C. If either is empty, the result is 0 seconds, if both are filled the subtraction is done.

Either way, the cell contains a Duration, and the SUM(C) formula will work in the footer row.

Regards,

Barry

Hi theemurman,

I agree with Barry about the Mysteries of 'Date & Time' and 'Duration' formats in Numbers. They can be confusing.

Here is a way to get over the "crossing midnight" problem when Start and End times are both entered on the same day.

Test for a negative Duration and add one day (+"1d")

Columns A and B are formatted as Date & Time (Date None).

Enter the Start and End times and don't worry about the date. Just do it.

Column C is formatted as Duration (Custom Units > Hr Min).

Formula in C2 (and Fill Down)

=B2−A2

Now test for the "crossing midnight" problem.

Formula in D2 (and Fill Down

=LEFT(C2,1)

If there is a negative Duration, the leftmost character wil be a minus "-" sign.

Formula in E2 (and Fill Down)

=IF(D2="-",C2+"1d",C2)

This tests for a minus sign.

If the Duration is negative, the formula adds one day (+"1d") to give the correct result.

If the Duration is positive, that is the correct result.

This will only work for two consecutive dates (only one "crossing midnight")

If you follow Barry's advice and enter the Start and End times on the actual Start and End days, this is illustrated in Row 8.

Start time was entered on 29 June. End time was entered after midnight (on 30 June). The formula will work because the dates are different (no negative Duration).

Regards,

Ian.

P.S.Midnight may show as 00:00 or 12:00 am depending on your cell format (and perhaps Region settings).

Hi t'

(Date and) time and Duration calculations can be difficult. If you can provide a screen shot of your table and the formulas you are using, it will likely be easy to determine where and why things are going wrong.

To take a screen shot of part of the screen:

• place the mouse pointer at the top left corner of the area you want to include.
• press shift-command-4 (the pointer will change to a circle with cross hairs)
• prlss and hold the mouse button and drag to the bottom right corner of the area to be included in the screen shot.
• release the mouse button.

The screen shot will be saved on your desktop as Screen Shot date time .png

To post a screen shot:

• place the insertion point where you want the image inserted
• click the camera icon in the bar above the composing pane
• click Select file
• navigate to Desktop, click Date modified to sort by this column and bring Screen Shot to the top of the list
• click the fine name to select it.
• click Choose
• click Insert image

Regards,

Barry

PS: Your initial posi indicates you are working in "Pages." Is this correct?

B

Many are the mysteries of Dates, Times and Durations.

Heres one more:

Every Date or Time displayed in a cell in Numbers is actually a Date & Time value formatted to show only the date part or to show only the time part.

If you enter only a Date, Numbers automatically sets the time part to 00:00:00 (midnight, at the beginning of that day).

If you enter only a time (of day), Numbers automatically sets the date part to the date on which the entry was created.

That last fact can be important in your current project.

If your gig begins and ends on the same calendar day, make sure you enter the start and end times on the same day.

If your gig begins on one day, and ends after midnight (ie. after the next calendar day has begun), make sure you enter the start time on the day before you enter the end time.

It does not matter to the calculations whether the start and end times are entered on the same day that each one occurs. What matters is both entries are made on the same day if both occurred on the same day, and that end time is entered a day later than start time if the shift ended on the calendar day after it started.

Regards,

Barry

Edit (too late to beat the Time-out)

Corrected screen shot is Left(C)

Groan 😊. I do this all the time.

Regards,

Ian.