Calculating time involving midnight

See the "Ian2" formula (i.e. the formula in the last row) in the table in the very last post (not the posts before that one) of this thread.

As far as I know, Ian's approach is the only one that will automatically give you what you want when "crossing midnight" but will also return the correct results in other situations.

SG

Hello

You may try something like this. It assumes time span for each entry is less than 24 hours. (Divide entry in case it is greater than or equal to 24 hours)

Table 1 A1 date A2 2015-08-01 A3 2015-08-02 A4 2015-08-03 A5 2015-08-05 B1 start B2 2015-08-07 23:00:00 B3 2015-08-07 23:00:00 B4 2015-08-07 08:00:00 B5 2015-08-07 08:00:00 C1 end C2 2015-08-07 02:30:00 C3 2015-08-07 11:00:00 C4 2015-08-07 07:00:00 C5 2015-08-07 17:00:00 D1 hours D2 =MOD(TIMEVALUE(C2)-TIMEVALUE(B2),1)*24 D3 =MOD(TIMEVALUE(C3)-TIMEVALUE(B3),1)*24 D4 =MOD(TIMEVALUE(C4)-TIMEVALUE(B4),1)*24 D5 =MOD(TIMEVALUE(C5)-TIMEVALUE(B5),1)*24 E1 duration E2 =DURATION(,MOD(TIMEVALUE(C2)-TIMEVALUE(B2),1)) E3 =DURATION(,MOD(TIMEVALUE(C3)-TIMEVALUE(B3),1)) E4 =DURATION(,MOD(TIMEVALUE(C4)-TIMEVALUE(B4),1)) E5 =DURATION(,MOD(TIMEVALUE(C5)-TIMEVALUE(B5),1))

* Table is built with Numbers v2.

Regards,

H

Below is Ian's formula copied from the other thread. In Numbers 3 it handles the "crossing midnight" problem nicely without one having to go in and fiddle with the date. No change to the formula needed if the time span is greater than 24 hours.

SG

Hello

I do repeat that naive subtraction of quasi time only values in Numbers is ponentially dangerous and should be avoided because they are always date-time values of which date part contains the date of the input.

You have been warned.

H

NJ786 wrote:

Thanks a lot, it works great, but the value returned is in decimal I must get it in hours and minutes e.g 03:30.

can u help.

To turn the decimal value into duration, you can use the DURATION() function.

The combined formula in C3:

=DURATION(0,0,DUR2HOURS(IF(DUR2HOURS(B−A)<0,B+"1d"−A,B−A)))

Note that in Numbers 3 you can always just click a cell and glance lower left to see the 'Actual' full date-time value. In my example here both columns A and C show 8/6/2015 folllowed by the time at the lower left, even though the cells display only the time. So using cells as "time only" displays is really that dangerous as long as one realizes they contain date-time strings. In fact in the templates that come with Numbers 3 Apple make ingenious use of the ability to just do subtraction. It's a real convenience that means most of us don't have to struggle with MOD() function.

SG

Hello

I really do not see virtue of Ian's formula, for it uses ad-hoc double standards.

That is –

a) if time is inputted as true date-time value, you may simply subtract the two date-time values;

b) if time is inputted as quasi time-only value, you must ignore the date part and subtract the two true time-only values extracted from quasi time-only values.

In case of a), the given date-time value in C in the 2nd example, where B > C, should be considered input error.

In case of b), date part is meaningless and must be ignored to guarantee the correct calculation.

Nevertheless, the presented formula calculating time difference from B to C arbitrarily applies a) for B <= C and b) for B > C.

Since the current case under discussion is b), the formula should apply b) only. Otherwise it could yield wrong result – e.g., i) when value in C is edited on later date than B (due to found typo etc), it yields unexpectedly large value and ii) when value in B is edited on later date than C, it always yields C - B + 1d, which can be even negative.

Regards,

H

NJ786 wrote:

can u also tell how to get rid of these red icons which appears next to empty fields.

Those are caused by the preceding blank cells in columns I and J. To suppress the display of an error triangle you can wrap a formula in IFERROR(), like this

=IFERROR(<the original formula>,"").

If the formula results in an error this tell Numbers that instead of displaying an ugly triangle it should insert the value you've put after that final comma, in this case "" or blank. Obviously, you want to do this AFTER you're sure you have succeeded in debugging everything. Otherwise, you could be hiding genuine errors.

So I think your combined formula in column K with the times (actually date-time strings🙂) in I and J would be this:

=IFERROR(DURATION(0,0,DUR2HOURS(IF(DUR2HOURS(J−I)<0,J+"1d"−I,J−I))),"")

SG

Hiroto wrote:

In case of a), the given date-time value in C in the 2nd example, where B > C, should be considered input error.

Hi H,

I think you're thinking more like a mathematician than some of us other spreadsheet users who just want to get the thing to do what we want it to do!

Of course it's "input error" not to input a later date for an early morning end time that follows a late evening start time (thus resulting in B>C). But some of us would like Numbers to anticipate that common human error and give us the result we "meant" in the context of a timesheet. From my testing (in Numbers 3; maybe Numbers 2 is different) Ian's formula does that admirably. And that same formula still performs as expected if we do go in and change the date. And it still gives the result we expect if the end time is more than 24 hours later.

(An aside: I may be wrong, but I did a quick comparison, and I believe it is much easier in Numbers 3 to see the date-time contents of a cell than it is in Numbers 2. All you have to do is click the cell and glance lower left.)

SG

Hello

Well, let me explain the issue with some concrete examples.

E.g. 1. When I input 23:30 in B2 and 02:30 in C2, both on date 2015-08-02, which will result –

B2 2015-08-02 23:30:00 (date-time value, only showing time in cell by format) C2 2015-08-02 02:30:00 (date-time value, only showing time in cell by format)

and Ian's formula (for B > C) will yield C2 - B2 + 1d = 3:00, which is fine, and now change C2 to 02:40 (by re-entering 02:40) on date 2015-08-04, which will result –

B2 2015-08-02 23:30:00 (date-time value, only showing time in cell by format) C2 2015-08-04 02:40:00 (date-time value, only showing time in cell by format)

and Ian's formula (for B <= C) will yield C2 - B2 = 27:10, which is wrong.

E.g. 2. When I input 8:00 in B3 and 10:00 in C3, both on date 2015-08-05, which will result –

B3 2015-08-05 08:00:00 (date-time value, only showing time in cell by format) C3 2015-08-05 10:00:00 (date-time value, only showing time in cell by format)

and Ian's formula (for B <= C) will yield C3 - B3 = 2:00, which is fine, and now change B3 to 07:50 (by re-entering 07:50) on date 2015-08-07, which will result –

B3 2015-08-07 07:50:00 (date-time value, only showing time in cell by format) C3 2015-08-05 10:00:00 (date-time value, only showing time in cell by format)

and Ian's formula (for B > C) will yield C3 - B3 + 1d = -21:50, which is wrong.

These wrong results come from the wrong assumption that quasi time-only value contains meaningful date, whether in case B <= C or B > C.

This is not theoretical but practical concern. And in my opinion, Ian's formula is illogical and inconsistent as explained in my previous post and thus misleading and prone to error.

Regards,

H

Hiroto wrote:

and Ian's formula (for B > C) will yield C2 - B2 + 1d = 3:00, which is fine, and now change C2 to 02:40 (by re-entering 02:40) on date 2015-08-04, which will result –

B2 2015-08-02 23:30:00 (date-time value, only showing time in cell by format) C2 2015-08-04 02:40:00 (date-time value, only showing time in cell by format)

and Ian's formula (for B <= C) will yield C2 - B2 = 27:10, which is wrong.

Hi H,

After double-checking I'm pretty confident that 27:10 is not wrong.

Start with 30 min before midnight on 2Aug.

To midnight on the next day, 3Aug, would then be 24:30 (the 30 min on 2Aug plus 24 hours on 3Aug).

Then you have another 02:40 to get to early morning of 4Aug.

So you have 24:30 + 02:40 which is 26 hours plus 70 minutes or 27:10.

The other case you cite (where the result is negative) I believe is also correct but, in the context of a timesheet, meaningless. The negative value will immediately alert the user to a problem, and, with Numbers 3, it's really easy to just look lower left to see what went wrong with the dates and make an adjustment.

SG

Hi NJ,

I have followed this thread with amusement 🙂. In Numbers, we have a Date & Time format. That is an instant in time since the Base Date chosen by Apple (midnight at the start of the first day of January in the year 1904). That is the first Leap Year in that century, and it becomes easy to project forwards in time to predict other Leap Years. That is why Apple chose that Base Date.

Think of Date & Time format as Full Date & Time (The date and time when the entry was made):

Formula in C2

=A2−Apple Base Date::$A$1

Formula in F2

=D2−Apple Base Date::$A$1

Formula in G2 (the formula that you want)

=D2−A2

That will work if Start (A2) and Finish (D2) are entered on the days of Start and Finish.

If Start and Finish are entered on the same day (after "crossing midnight") then we need to add "1d"

Cheers,

Ian.

Yellowbox wrote:

Formula in G2 (the formula that you want)

=D2−A2

That will work if Start (A2) and Finish (D2) are entered on the days of Start and Finish.

If Start and Finish are entered on the same day (after "crossing midnight") then we need to add "1d"

Hi Ian,

I think the two formulas in bold do what you are describing (the first for decimal hours, the second for duration).

SG