Arithmetics with dates and times?

[Hvergelmir]

Hi,

I have a fascinating issue with LibreOffice:

Version: 7.0.4.2 (x64)
Build ID: dcf040e67528d9187c66b2379df5ea4407429775
CPU threads: 20; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

I’m trying to track my work hours with LibreOffice Calc and created myself a worksheet which looks as follows (describing columns):

• A: Date
• B: Start time (wall clock time)
• C: End time (wall clock time)
• D: Break (in minutes)
• E: Gross hours (End time - Start time)
• F: Net hours (End time - Start time - Break)
• G: Required (number of hours required to work on a given day)
• H: Overtime (should be calculated)

I suppose the formatting of the cells doesn’t really matter here, as it’s just representation, but please ask back if it somehow matters after all.

Now the Date column isn’t really relevant at all. Gross hours is the first column to be calculated and that appears to work fine (possible exception, which I haven’t had would be working until after midnight). So the value in there will always end up being a positive number of HH:mm …

The same (currently) happens for Net hours, but arguably it could happen that the overall outcome of this value could be negative.

The issue is with Overtime, which is a column that must be able to take the “overtime” be it positive or negative. The calculation here is End time - Start time - Break - Required with the formula =Fx-Gx (x being a placeholder for the row number).

Now there are days when I work less than Required to take off some of the accumulated overtime hours. In this case the calculation ends up with a value of 24:00 h minus the overtime I took off.

How can I teach Calc to give me a negative value for the time here, as result of the calculation?

[Lupp]

Be aware of problems you will get if EndTime isn’t a TimeOfDay (TOD) on the same day as StartTime.
You should generally enter Start and End as date-time-stamps (using a shortcut e.g.) instead of typing what you read from a wall clock.
Be also aware of the fact that silly “DST” can spoil yor calculations.

[keme]

Time formats come in two flavors, Time of day and Accumulated time.

With general format, time data will be formatted as time of day. You have to explicitly apply accumulating format, which is what you need. You do that by enclosing the accumulating section in square brackets.

• Select the offending cell and pick menu item Format - Cells ..., then the Numbers tab.
• Select a time format. It will appear as something like HH:MM:SS in the format code field at the bottom of the dialog.
• Add brackets, so it appears like [HH]:MM:SS

[Lupp]

Generally Start and End of a duration to be calculated should be entered/created as date-time-stamp values (by a shortcut e.g.) instead of typing something read from a wall clock. In case of working time you would othertwise get a serious problem as soon as it once transcends midnight. I would assume in addition that the results will be multiplied by an hourly rate. Therefore these results shouldn’t be calculated based in the spreadsheet-implicit unit “day” which definitely is introduced for calendaric point-in-time values.
In addition also formats like [HH]:MM are misleading when used for durations. Even if sticking to the “unit” day, the fundamental distinction between DOT and duration should be regarded by using the format code (e.g.) [HH]" h "MM" min" avoiding the colon for durations.

See example ask294810workingTime.ods.

[Hvergelmir]

Thanks, unfortunately it appears I cannot upvote as of yet. Need five votes to upvote … uhm. Thanks anyway.

[ajlittoz]

For your column H, choose a format where the hour part is enclosed in square brackets like [HH]:MM or [HH]:MM:SS. They are present in the built-in codes for Time.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

[mikekaganski]

Check opening curly bracket { in the second format string.

[ajlittoz]

Thanks for the typo alert. I’ve always found that the “PC”-style keyboard layout was more error prone than the Mac-style with regard to “similar” special characters.

[newbie-02]

i needed to apply a format string like HH:MM:SS;[ROT]-HH:MM:SS to get negatve times activated,

be aware that there are two ‘points’:

‘time of day’ values may produce problems running over midnight,

‘date-time’ values may inject small deviations on subtracting times near to each other as you calculate with two numbers being relatively big compared to the result, ‘catastrophic cancellation’ is a weakness in floating point arithmetic,

choose your poison

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

[Lupp]

For the current century the date part INT(NOW()) is less than 80000 and needs about 16 dyadic digits at most. The Double representation has therefore a resolution in the time part of 2^-36 days or better. That’s about 1 µs (1E-6 s) as absolute value 80 years from now. Not too bad for a work-time sheet. Catastrophic cancellation not in sight so far.

Did you have a glance at my example sheet? Column K there surely has some negative values formatted using the DOT style. They are correctly shown without special measures concerning the sign.

[newbie-02]

@Lupp:
‘Catastrophic cancellation not in sight so far’ … not too far away, small impact but it made it already into the FAQ, see: the time sample there:
it’s less the absolute number of digits but more the result being 23 dyadic ‘ranges’ below the ranges of the operands, thus pulling each deviation from dec->bin conversion 23 bits to the left and thus giving them weight … whilst not corrected by meaningful countermeasures …
having 3 as a prime in 24 and 3 and five in 60 is fine to have smooth values for e.g. the third of an hour, but makes it difficult to find binary representations for decimal ‘even’ values, they are ‘odd’ and rounded, thus contain deviation, and cancellation blows them up … there will be worse sample surely …

[Lupp]

“… and thus giving them weight …”
The weight of a µs is a µs. But indpendent of the reslution you can always find examples where “a specific something” can be calculated in two “mathematically equivalent” ways that produce different results due to specifics of “machine arithmetic”. In addition conversion to and from formats preferred by humans can irritate. With respect to everyday time-values there are lots of additional complications with an impact on the results outrivaling the resolution by a high order of magnitude. Let’s apply sound rounding, and results will be precise and reliable enough.
We can, however, never guarantee, that there will be twice the same result if a a time value is calculated on two different ways and rounded to seconds. A difference of 1 s (or even 5 s) we should regard negligeable for a comparison then.
[Some internal structures of LibO are prepared for a time resolution of 1 ns (1E-9 s). Will not change the principle when fully operative!]

[newbie-02]

i’m neither finished understanding time values, nor their handling in calc,
just wanted to raise the awareness that:

• the choice of a suitable number representation is not trivial,
• a system in which the ‘human minute’ is represented by an endless numerical value like 0.000694444444444444~~ has its own pitfalls,
• a mathematical model which calculates as difference between 12.02.21 05:01 and 12.02.21 05:00 ‘0,000694444439432118’ while 00:02:00 minus 00:01:00 results in
  ‘0,000694444444444444’,
• and a spreadsheet which then (questionably?) displays this as
  ‘0,00069444444444444400’ while calculating with
  ‘0,000694444444444444433680868994202…’
  can already pose challenges for ‘users’
  similar to a www.DeepL.com/Translator (free version) ‘translating’
  ‘0,000694444444444444433680868994202…’ into
  ‘0,00069444444444433680868994202…’
  the latter may explain some of the bullshit i’ve posted here in the last few months

[newbie-02]

'i’m neither finished understanding time values, nor their handling in calc, ’ …
did i hear rumors about being silly? those people may try to explain to me why 2099-12-31 23:59:59,50 is displayed as:
2100-01-01 00:00:00 with a format string of JJJJ-MM-TT HH:MM:SS while as:
2099-12-31 with a format string of JJJJ-MM-TT,
and we didn’t yet talk about

• negative values for days before 1899-12-30,
• ex$el and calc deviating in
  having or not a February 29, 1900,
  and others,
• y4k problem,
• leap seconds …
  (i already have! heard about ‘posix’ and it’s limitations …)