duration

Hi Cliff,

You wrote "That does not seem to work. "

Actually it does, when it is understood that in Numbers, every entry of a time of day or of a date results in a Date and Time value in the cell, and every cell displaying a Date or a Time (of day) or both contains a Date and Time value.

If only a Date part is entered, the Time part is automatically set to 00:00:00, midnight at the beginning of that date.

If only a Time part is entered, the Date part is automatically set to the date on which the entry was made.

Subtracting the earlier Date and Time entry from the later one will return the Duration value of time elapsed if the full Date and Time entry is made for the two entries, but will return a negative value if only the time parts of the two values are entered, both entries are made on the same day, and the period begins later on one day than the time it ends on the next day (as in your example).

Here's an example,using the times you supplied, starting 'today' (Oct 16) and ending early 'tomorrow' (Oct 17), and entering both Time and Date parts of the start and end times (in row 2), but only the Time parts of those Date and Time values on row 3. In both lines, the start and end times were entered on the same date (Oct 16).

Column D contains the shorter formula shown below the table, entered in D2 and filled down to D3.

Note that the result in D3 is negative. This is a correct result for the values that are in cells C3 and D3.

Both Time Part only values were entered on the same date, and Numbers automatically set that Date (Oct 16, 2022) as the Date part of both D&T values, making 5:13 AM almost 16 hours before 9:11 PM.

Column E contains the same formula (C2-B2) as column D, but that formula is enclosed in an IF statement.

The formula assumes that the beginning and end (Date and) Times will always be entered on the date that each occurs OR will both be entered on the same day.

IF uses C2<B2 to determine whether the C2 Date and Time is earlier than the B2 Date and Time. IF that statement is TRUE, the most likely cause is that both were entered on the same day, and and only the Time part was entered, and IF chooses the longer option that adds one Day to the (negative) duration resulting from C2-B2. If the comparison returns FALSE, IF jumps past the long formula, does the simpler C2-B2 calculation, and returns the positive result.

Regards,

Barry

Hi Barry,

A simpler formula is to add 1 day (+"1d") in the case of crossing midnight.

Numbers has automatically added the date to time-only entries, as revealed by changing the display to full Date & Time.

Formula in D2: IF(C2<B2,C2−B2+"1d",C2−B2)

That may be easier to understand than a formula using the DURATION function.

Regards,

Ian.

Subtract one from the other. Format the cell to show the units you want (hours and minutes)

You marked your problem solved but your latest post indicates otherwise

formula in D2 is =C2-B2

What answer do you get for the difference between 5:13AM and 9:11 PM (with the same date)?

Hi Ian,

Agreed.

Adding "1d" to the (negative) result of C2-B2 returns the same (correct) duration as does adding one day using the DURATION function.

I used the function (and kept the unit names visible in each of the unused arguments to introduce the DURATION function to the user and provide some information as to the structure of that function. Not the most efficient wrt typing the formula, but it does the job, and provides a view of the function that may prove useful in future.

Regards,

Barry

A less obvious way to correct it, because the MOD function does not mention it works on durations, is

=MOD(C2−B2,"24h")

or

=MOD(C2−B2,DURATION(0,1))

or

=MOD(C2−B2,DURATION(0,0,24))

The first one did not require me to format the cell to display as hr min but the second two did. But using DURATION in the formula seems more proper and is probably more universal ("24h" may not be interpreted as 24 hours in another language).

Hi Ian,

Note that +"1d" may not work if region/language is set such that the word for day in the language does not begin with the letter d.

SG