When i try to sum all the total hours, including empty cells, i get this negative result. What am i doing wrong?
test.ods (11.5 KB)
- You are misusing a format made for TimeOfDay values when calculating durations.
A format code likeHH:MMis expected to suppress a date part possibly contained in the actual value of the cell.
This can easily happen unnoticed if you restrict the hours part (HorHH) to a maximum of 2 digits. Use [HH] instead to at least avoid unnoticed errors of the kind. - You expect the sheet will guess correctly that
04:00behind a22:00is meant to mean “the day after”. Sheets don’t guess generally - and if they do they’re mostly wrong. - You need to enter starting time and ending time including the day (date) to disambiguate the situation.
- You shoudn’t tell or think “hours” if you mean “time” or “duration”. The usage of clear and unambiguous terms helps to avoid errors of many kinds. Decide consciously what units you want to use, and design your formulas respectively.
- If you can assure that the ending time never can be 24 hours or more after the starting time, you can do as @Villeroy suggests. Stay aware, however, of the fact that this is a hazardous workaround. Some professions need to regard stand-by times that may exceed a full day.
- If calculating durations as differences of date-time values (or DOT values) you need to accept the predefined unit of 1 day which is used for the calculation even if a result is shown as a (misleading)
3:30(e.g.). The actual value of the cell would be0.1458333...for the example. To get the number of hours you need to multiply that result by 24 (assumed 1 d = 24 h – which is currently broken twice a year in many countries).
2 Likes
No, he is using [HH]:MM actually. At least for the sum.
Yes, and only for the sum.
The intervals are always below 1, so the formatting makes no difference.
Set [HH] also for the differences per row, and the error will easily get noticed (negative “durations”).
When you remove the time formatting, you see that “times” are decial fractions, fractions of days.
0.25 → 06:00
0.5 → 12:00
0.75 → 18:00
06:00 - 18:00 actually calculates
0.25 - 0.75
You have to add one day in order to calculate the time difference from 18:00 until next day’s 6:00.
=C2-B2+(B2>C2) which adds 1 if B2>C2
1 Like
You simply made a very common understanding error.
When you write something looking like a date or a time, Writer encodes this as a date-time floating-point value. The integral part of this value is the number of days since the epoch and the decimal part is the fraction of the day, where 0.0 is 00:00:00 up to 0.9999… for 23:59:59.999…
In your row 7, your starting hour is 20:00 and the ending hour 9:00, presumably on next day but encoding shows the date part is the same. Therefore, when you subtract you get a negative result which can be masked if you chose an incorrect formatting. [HH]:MM is dedicated to full number of hours without being clipped to 0-23.
Computing a time span requires some precaution, notably checking for midnight crossing.
Hallo
change the Formula in D2 to =C2-B2+(C2<B2) and pull down.