Numbers: Omitting date from time for duration calculation

A different approach from SGIII's is a formula that extracts the HOUR and MINUTE components from the date fields, thereby eliminating any difference in the underlying DATE() of the cells.

It's a little complicated, but I'll work through my thinking (there may be optimizations I haven't thought of yet).

Here's the table I was working with (note random dates added to the first row to show that it is not affected by days):

I start with TIMEVALUE(), which extracts the time component of a date (i.e. strips the day/month/year elements).

=TIMEVALUE(B3)

returns 0.458333 as the fractional part of the day for the scheduled start, and

=TIMEVALUE(D3)

returns 0.386111 as a corresponding value for the actual start time.

I can calculate the difference by simply subtracting one from the other and multiplying it by 24 * 60 to get a number of minutes:

=ABS(TIMEVALUE(D3)−TIMEVALUE(B3))×24×60,

which gives me 104 minutes in this case (the difference between 9:16 am and 11 am

with this value I can convert this to the number of hours and minutes using QUOTIENT() and MOD()

Now I have a number of hours and a number of minutes, which I can pass into DURATION() to convert into a standard Duration value of '1h 44m'.

Because there's a lot going on here, I used LET() to break out the individual steps and create variables for the components. My full formula in D3 looks like:

=LET(scheduled_start,TIMEVALUE(B3),

    actual_start,TIMEVALUE(D3),

    difference,ABS(actual_start−scheduled_start)×24×60,

    hour_difference,QUOTIENT(difference,60),

    minute_difference,MOD(difference,60),

    DURATION(,,hour_difference,minute_difference,,)

)

As you have discovered cells containing a date or a time always contain both date and time even if you don't display both. If you don't supply a date when entering the value Numbers will assume the date on which you made the entry.

However, there is an easy workaround for problems such as yours. If you pre-format the cells as Text (instead of Automatic or Date & Time) and enter just the times, Numbers will see those times without a date and assume you always mean today. If your event start and end times are always on the same date this will simplify your data entry.

If your events are longer than one day then you will probably end up having to explicitly enter the starting date and time, and ending date and time.

SG

Numbers, unlike Excel, has built-in capability to do all the confusing modular math for you when need to find the difference between two times.

If you don't want to enter "times" as Text, which results in the simplest formula, then you can easily enter as Date & Time as usual and strip out the date with a short formula.

In this example, with the later time in C3 and the earlier time in B3, the formula can be simply:

=(TODAY()+TIMEVALUE(C3))−(TODAY()+TIMEVALUE(B3))

TODAY - Apple Support

TIMEVALUE - Apple Support

SG

SG

Hi fergo84,

OK. Here we go with SG's suggestion to enter times as Text.

The TIME function is your friend.

Columns A and B are formatted as Text. Enter the times in those columns.

Formula in C2 is TIME(TEXTBEFORE(A2,":"),TEXTAFTER(A2,":"),0)

Formula in D2 is TIME(TEXTBEFORE(B2,":"),TEXTAFTER(B2,":"),0)

Formula in E2 is D2−C2

Fill down.

Note that the Text columns (A and B) are automatically aligned left; the Times columns (C and D)) are automatically aligned right.

No! the result in E6 is negative because the times crossed midnight.

In column F we correct that "crossing midnight" problem with the IF function testing and then perhaps adding one day (+"1d")

Formula in F2 is IF(C2>D2,D2−C2+"1d",D2−C2)

Fill down.

You can delete column E and hide columns C and D

Regards,

Ian.

Using text is probably the best way. The "time" values will be what they really are (times without dates, not times with nonsensical dates), the automatic format for the result will be correct (hours and minutes) vs having to adjust it to remove days and milliseconds or show hours and minutes, and the formula is a simple subtraction. But, regardless, here is another way to do it with the date&time format for no reason other than to show another way to work with date&time values.

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

or

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