I have a fascinating issue with LibreOffice:
Version: 220.127.116.11 (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?