Calculating time involving midnight

Hmm. You really don't understand the issue in the context.

I am talking about quasi time-only value, which is a date-time value of which date part is hidden by format, as observed in the FLIGHT TIME UTC columns of the screenshot of the original questioner NJ786 of this thread.

In that context, the following data pair should yield time difference (C-B) being 3:10, not 27:10. (E.g. 1)

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)

In that context, the following data pair should yield time difference (C-B) being 2:10, not -21:50. (E.g. 2)

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)

However, Ian's formula returns different results, which are plain wrong in the context. The cause of these errors have been already explained more than once.

H

PS. Do not take my criticism personally. I have nothing against Ian either. And I usually don't comment on other's answers. But I do presume to do so here because you keep recommending problematic formula to not well-informed questioner.

Hi H,

Not taken personally here (I can't speak for NJ786 or Ian). However, we all may be more well-informed than supposed, especially about what is effective in making practical use of Numbers 3.🙂

I can't think of a context where I would want the time between 2015-08-02 23:30:00 and 2015-08-05 02:40:00 not to be 27:10, and instead be 3:10 as you suggest. I would have put that second date in there for a reason (a long stretch of hours on duty comes immediately to mind).

On the other hand, I can easily think of a context, which sparked NJ786's question, where I would want the time between 2015-08-02 23:30:00 and 2015-08-02 02:40:00 to be 3:10. That situation can easily occur when entering both start and finish times at once (in practice that happens quite often). As you know, Numbers automatically assigns the same date, even if the finish time has "crossed midnight." Ian's approach removes the bother of always having to go in and "finger" or "touch" the date each time in those cases. I think that was NJ786's original question.

In the context of a time sheet or schedule a negative result is never expected and is therefore a sure tipoff to double-check the contents of the cells. Double-checking the dates in cells displaying time only is really easy to do in the new Numbers 3 interface.

Sure, this approach may not be 100% "correct" in all possible contexts. But it's a great practical solution in the most common context, and simply enhances the elegant solutions Apple has provided in the templates provided with Numbers 3. They use simple subtraction (and addition) and don't require us to delve into the mysteries of MOD(), an approach which in the wrong hands (certainly mine) is rife with opportunity for confusion leading to a whole other, much bigger, set of possible errors. Hats off to Ian.

SG

Please read my replies more carefully in the context of the original question or you're wasting everyone's time.

---

Just for readers who may stumble across this thread in the future, I repeat naive subtraction of quasi time-only values in Numbers is potentially dangerous and should be avoided because they are always date-time values of which date part contains the date of the input.

Proper methods to calculate time difference (duraction value) between given two date-time values are as follows.

a) If B2 and C2 contain true date-time values, of which dates and times are both guaranteed to be meaningful data, we may simply calculate the time difference (duration value) from B2 to C2 by the formula:

=C2-B2

b) If B2 and C2 contain quasi time-only values, of which dates are hidden and not guaranteed to be meaningful data, we may calculate the time difference (duration value) from B2 to C2 by the formula:

=DURATION(,TIMEVALUE(C2)-TIMEVALUE(B2))

or

=DURATION(,MOD(TIMEVALUE(C2)-TIMEVALUE(B2),1))

The first formula will allow negative result. The second formula will add 24 hours to negative result. (Usage of quasi time only values in B2 and C2 should imply that the time difference from B2 to C2 does not exceed 24 hours, which gives reasons for modulo 1 operation.)

Regards,

H

Hiroto wrote:

The first formula will allow negative result. The second formula will add 24 hours to negative result. (Usage of quasi time only values in B2 and C2 should imply that the time difference from B2 to C2 does not exceed 24 hours, which gives reasons for modulo 1 operation.)

Hi H,

That, I believe, is the crux of the difference of opinion here. Displaying time-only in a cell containing date-time (is that what you mean by "quasi time"?) does not necessarily imply that one does not ever want a period to exceed 24 hours.

What if you have a schedule where you mainly deal with periods under 24 hours but occasionally you have a period over 24 hours? (That's a pretty common occurrence in some professions and activities). Would one change the whole visual display (make wider columns to display ugly dates too even when dates are not the focus) just to accommodate the exceptions to the general rule? I wouldn't want to do that.

Ian's approach handles the long periods correctly all in the same formula that can be filled down the column. From my testing, your examples of the "correct" approach do not. You would have to remember to input different formulas to handle each case. Easy for you. Not so easy for many of us. Having a single formula makes it easier to maintain a spreadsheet.

Apple does a fair bit of "naive subtraction of quasi time-only values" in the attractive templates they provide with Numbers 3. Is that approach potentially dangerous? Theoretically yes, especially in the old Numbers interface you have there. In Numbers 2 it's just does not seem to be that easy to see the actual contents of a cell, and thus it's easier to forget about the date part. But is it useful? I think so. So I'm going to go with their approach, with Ian's nice little modification, and recommend that others do too.

At the same time, I think it's good to bear in mind your general warning not to forget that there is no such thing as a "time-only" cell in Numbers. It's always date-time even if only the time (or only the date) is displayed. If the results aren't what you expect, in Numbers 3 just glance lower left to inspect the actual contents of a cell. The date will be there.

SG

Hi Hiroto,

I take no offence from your replies. I thank you and SGIII for an interesting debate on Date & Time in Numbers.

true date-time values, of which dates and times are both guaranteed to be meaningful data

That is true if times are entered on the correct date, but it is often human nature to fill in a time sheet with both Start and Finish times at the end of a shift.

Would you and SGIII please consider this "Crossing Midnight" document? It contains a column to alert a user who enters both Start and Finish times after crossing midnight.

The Log sheet has hidden columns to simplify the entry table. The Alert column tells a user to examine the Alert Details sheet.

Kind regards,

Ian.

https://www.dropbox.com/s/p5x3tqdlzhqnc2h/Crossing%20Midnight.numbers?dl=0

Oops, sorry.

Here it is in Numbers 2.3

https://www.dropbox.com/s/cylu0slhhe4hjyy/Crossing%20Midnight%20Numbers%202.numb ers?dl=0

Kind regards,

Ian.

The issue is simple. I have explained enough. If you do not understand, it is your problem, not mine.

Hello Ian,

Thanks for the sample file.

The fundamental problem is to assume quasi time-only value, of which date part is hidden, contains meaningful date.

Given B4 and E4 represent quasi time-only values in your sample table, your following formula:

H4 =IF(DUR2HOURS(E4-B4)<0,E4+"1d"-B4,E4-B4)

assumes that -

a) if E4 >= B4, E4 and B4 contain meaningful dates, i.e., E4 and B4 are true date-time value; and,

b) if E4 < B4, E4 and B4 may not contain meaningful dates but E4 - B4 > -1d holds.

Now let the user change the value in E4 from 23:59 to 23:58 on date 2015-08-09, where B4 remains the same as [2015-08-02] 22:00:00, then the resulting duration will be 169h 58m, without any Alert.

Or let the user change the value in B5 from 22:00 to 22:01 on date 2015-08-09, where E5 remains the same as [2015-08-03] 00:00:00, then the resulting duration will be -142h 1m, without any Alert.

etc, etc.

Solution is quite simple. Do not assume quasi time-only value contains meaningful date. Instead, always extract true time-only value from quasi time-only value by means of TIMEVALUE() function and use it in calculation, such as

H4 =DURATION(,MOD(TIMEVALUE(E4)-TIMEVALUE(B4),1))

This is what I keep demonstrating here.

All the best,

Hiroto

H,

As discussed above, your solution is not practical because it does not handle periods over 24 hours in the way one would expect. Anyone recording a shift of 27 hours 10 minutes surely does not want to get paid for just 3 hours 10 minutes! If you do, please come work for me.🙂

Ian's solution is practical. It automatically handles the "crossing midnight" input error gracefully in the way that a user would expect. It also handles periods over 24 hours correctly. Because it adds '1d' when starting time-date > ending time-date it will not give the theoretically correct result if the user really did mean to accurately measure a negative duration. But in the context of a timesheet or schedule, that's purely a theoretical shortcoming. In the real world we tend to go forward in time (even though sometimes it may seem as if we're going backward!) and if we see a negative number for time our brains tell us right away there is a BTCK ("between the chair and the keyboard") input error that needs a quick fix.

Please do keep in mind that in Numbers 3 the date part is never completely hidden. Nor is the time part. They're both right there, at the very top level of the user interface, no matter how the cell is formatted. That's a big improvement, in my opinion, over Numbers 2.

SG