Formulae required to distinguish between start and end time.

The underlying issue is that both dates and times are numbers which must be formatted to look like dates and times.
Times are decimal fractions of a day which reset at midnight.

Consider that the underlying numeric value of this moment is 42066.0642155093. Formatted for date and time is 3/3/2015 1:32 AM. The integer part is the date. The decimal the time.

One way of overcoming the math dilemma when computing times spanning midnight is to include the date also. That way subtracting 11:00 PM yesterday which would have been 42065.9583333333 from the above date / time we can reconcile the trans-midnight problem. It would correctly yield 0.109724652778823 which when formatted for hours and minutes (hh:mm) would be 02:38 or 2 hours and 38 minutes.

Does this help?

BTW day 1 is 1/1/1900.

Another BTW. When I did the subtraction above I was referencing a date / time generated by the NOW() function. It is volatile and when I did the subtraction time had advanced a few minutes.

Good clean explanation FlameRetired! Vince, Flame is absolutely right in saying that the best way to set this up is with the date to avoid a complicated mess.

I have not opened your sheet to look at your layout, but the logic that goes in place without dates (Again not recommended) would be as follows.

Please Login or Register  to view this content.

While that is a simplistic illustration of how to achieve it without a date, the more variables the more it gets complicated and many tend to get lost in logic.

Cheers