Negative time - displaying with 24hr wrapping or not

[Arioch]

I make a very simple table:

Code: Select all

  14:00 | 16:00 | =A1-B1

With Standard format C1 is getting -2:00
But with any of the Time class formats it suddenly turns around into 22:00

What is the logic behind that ? It should be at least uniform.

[acknak]

Greetings and welcome to the community forum!

Right. I see the same here.

So are you asking about the logic behind those results, or the logic behind the inconsistent formatting?

Times in Calc are stored as a decimal number of days. So the actual values are: 0.(6), 0.58(3), and -0.08(3).

Apparently, the default format uses a slightly different format for displaying times. Most of the category:time formats are "clock" times, and since most clocks can't display negative times, it forces the result to something that a clock can display. Think of -2:00 as two hours before midnight (24:00/0:00): 22:00.

If you want a time format that does display a negative time, use an elapsed time format: [H]:MM

[Villeroy]

Translated to unformatted numbers (aka fractions of days)

Code: Select all

14:00		16:00		-2:00
0,58333	0,66667	-0,08333

Number format [HH]:MM displays a negative day fraction -1/12 (a negative time span)
Number format HH:MM displays the hours reprepresenting 1/12 before midnight 22:00 (a point on the time scale).

On the spreadsheet time scale the point zero is 1899-12-30 00:00
-0,08333 formatted as full date+time is 1899-12-29 22:00
YEAR(-1/12) => 1899
MONTH(-1/12) => 12
DAY(-1/12) => 29
HOUR(-1/12) => 22
MINUTE(-1/12) => 0

If you want to calculate the time span between 16:00 and next day's 14:00:

Code: Select all

=A1-B1+(B1>A1)

adding one day if the end time is later than the start time