Using 24 hour calculations in numbers over different days

The issue you're running into is that apple has adopted ISO standards for recording Date and Time values. Any time your see displayed in Numbers is a Date and Time value, with only the Time part displayed. Any Date you see displayed in Numbers is a Date and Time value, with only the Date part displayed. No matter which part is displayed, a reference to the cell will always return the full Date and Time value.

When you enter a Date, Numbers automatically sets the Time part of the value to 00:00:00 (midnight, at the beginning of that date).

When you enter a Time (of day), Numbers automatically creates a full Date and Time value by setting the date part to 'today's date'. (This is the setting that affects your calculations.)

The base unit for Date and Time value is the day, so there are ways to correct for this apparent error.

Here is your basic formula for calculating the Duration between two Date and Time values where B2 contains the Start time, C2 contains the Finish time, and both are on the same calendar day:

=C2-B2

The result will be a Duration value

To avoid having to correct for a start time before midnight and a finish time after midnight, the straightforward solution is to enter those times during the calendar day on which each occurs. That's straightforward, but not always convenient.

If both times are entered on the same calendar day, you can apply a correction factor to the formula above:

=IF(C2<B2,C2+1-B2,C2-B2)

The result will be a positive Duration value.

Taking it one step further, you can use TIMEVALUE to strip the Date part from a Date and Time value, leaving a decimal fraction representing the portion of the day that has passed.

=IF(C2<B2,1+(TIMEVALUE(C2)-TIMEVALUE(B2),TIMEVALUE(C2)-TIMEVALUE(B2))*24

The result is a number representing the number of hours between the two times.

Here's a table showing five examples, and the results of the formulas above:

Regarding: "I also know to enter the times as "23:00" and "03:25", but do I have to do that??"

Yes. Numbers will not recognize a (date and) time entry without the colon.

Regards,

Barry

Not sure how your managing to get that result.

If you've entered only the Time part of a Date and Time value on both cells, the example should work as row 2 below. values entered were B2: 23:59, C2: 05:18. Cell format was set to Automatic, so the date part was appended (using March 16, the date the value was entered in each cell), but was not displayed. You can see the full value displayed in the editing box above the table.

In row 4, the full Date and Time value shown was entered in B4, only the time part was entered in C4 (then the cell was formatted to show both parts).

The formula was copied from your post above, pasted into D2, filled down to D4, then deleted from D3 (where it was displaying a zero—as it should in that row).

Entered without the colon, 1159 in B2 and 0518 in C2 are numbers, not times. Numbers drops the leading zero from the second number, and gives the correct result -640 (518 +1 - 1159 = -640).

There's no need to change the 'field" (cell?). If you enter a time in one of the accepted formats, Numbers will recognize it, complete the Date and Time value but appending the date of entry before the time part, and set the cell to display only the part that was entered. See the selected cell above.

How did you "change the field"?

Regards,

Barry