Simple Time/Currency calculation gives incorrect result in Calc

caelia · November 30, 2023, 7:27am

Hi! I have created a spreadsheet for tracking hours and income for some gig work I am doing. The relevant columns are as follows:

C: Start Time      [format code HH:MM AM/PM]
D: End Time        [format code HH:MM AM/PM]
E: Breaks          [format code [MM]]
F: Hours           [format code HH:MM]  Formula: =(Dn - Cn) - En  [where n = row number]
G: Income          [currency]
H: Hourly Rate     [currency]           Formula: =Gn / Fn

The column F formula gives the correct result, but column H is completely wrong. For example, here’s what I get in the first row of data (the only row I have entered so far):

C2: 06:30 PM
D2: 10:50 PM
E2: 20
F2: 04:00
G2: $68.00
H2: $408.00   << WAT?

This seems like a bug, but since I don’t use LibreOffice very much, I thought I’d best check here first. Have I done something wrong?

JohnSUN · November 30, 2023, 7:39am

Welcome!
Did you expect to see $17.00?
You should have used a formula like =G2/F2/24
The fact is that those 04:00 that you see in cell F2 are actually the number 0.166666666666667 (this is exactly the part of the day that makes up 4 hours), which is why division gives such a stunning result.

Wanderer · November 30, 2023, 7:49am

In case you wonder: The internal representation as fraction of a day makes calculations across days possible. So you get the right hours even for 2023/11/30-03:00 - 2023/11/29-22:00
.
But you have to convert, if you need real hours and many are surprised, when an amount of more than 24 hours seems truncated as the standard formatting will hide this (to be interpreted as date).

keme1 · November 30, 2023, 7:35am

Time is internally stored as a single number. The unit is days, not hours.
Hence, the time displayed, 04:00, is stored as 1/6 = 0,166666667. Dividing by 1/6 is the same as multiply by 6, which correctly gives the number you have.
To use a time reference as unit “hours”, you need to multiply the spreadsheet time value by 24. Try this instead:
H: =Gn/(24*Fn)

caelia · November 30, 2023, 7:59am

OK, fair enough. That’s easy to fix, and I appreciate your quick response (and the others). However, I would like to say that I don’t believe users of a mainstream business application like LibreOffice - should not have to know anything about internal representations in order to do simple calculations. To my mind, this is a SERIOUS usability issue.

But anyway, thanks for the help.

JohnSUN · November 30, 2023, 8:13am

Yes, you are absolutely right - this world is far from ideal! Users have to figure out why some months have 30 days, and others have 31, and another one has either 28 or 29 days… If only they made thirteen months of 28 days each, everything would be much simpler! Why are there 60 minutes in an hour and not 100? It’s so inconvenient. And there are an incomprehensible number of hours in the day. I understand you and sincerely sympathize with LibreOffice users (as well as users of Microsoft Office, Google Sheets, Gnumeric and generally all the inhabitants of this planet)

mikekaganski · November 30, 2023, 8:28am

You had some expectation, basically already thinking that you know the representations. You were wrong. And the main problem here is not that you need to know an “internal” representation (no, it is not “internal”, but is the external, documented, and very consistent across the whole industry), but the idea that by formatting cells, you change the data in the cell. And that is the thing that any user of a spreadsheet software needs to learn, that formatting is only how things look in cell, but does not change things. A cell may contain a number; and no matter if you format it as text; or as number; or as date; or as time - it will still contain that same number; and it will be used in whatever calculations you use this cell…

koyotak · November 30, 2023, 10:05am

I remember me that earlier time clocks had counted one hundred times per minute: 100 sec = 1 min. But the medieval people swaps onto today and into future. Look out: inch, feet, Zoll, Pfund, Doppelzentner, ounce, kg (not: Kg) as basic unit (!), HP (Pferdestärken „PS“). Those older mankind are using the Abacus, while it is unable to calculate mathematically, much more than counting fingers digits.

On the other hand: μC-programming cannot operate in mathematical modus. Operations are: moving „0“/„L“ (lower electric potencial) or „1“/„H“ (higher potencial) from memory cell to next memory cell or change the potential within a cell. Example: to multiplicate 56 with 10 cause a move every memory cell (not equal with CALC-cell!) value in one step to left and set a „0“ in the now empty memory cell right.

The visible picture you see in a CALC-cell is not that CALC has been operated with. The procedur of calculate and depict a DATUM (Latin: take for granted, gift, item of guilty) is a very komplex task in setting leap years cause 1 year has about 365.25… days with non fixed holydays. You remember perhaps that time on one location isn’t the same time on another remoted location. CALC/EXCEL does calculate only in days „[DD]“-unit in decimal numeral modus. CALC is than searching in a big matrix like a national calendar to find the correct weekday, holyday, month in national format, leap year type to paint that picture you will see in that cell.
To complicate this 1 year has a variable number of days so the earth is staggering surround the sun:

365,24219052 Tage = 365 d 5 h 48 min 45,261 s as a “tropical year“,
365,2563604167 Tage = 365 d 6 h 9 min 9,54 s. as a „sidercal year“,
365,259635864 Tage as a „abnormality year“.

That’s and even more declares a astronomic DATUM, which collapse of our primitive human brain by watching a beautiful coloured calendar and just as a shown CALC-area/space.

Explicit in CALC:

nominal 365 and 366 days a year and a leap year (by using the intern calendar-matrix),
calculating exactly with 365,2425 days a year without respect the real astronomic year so that differences are insignificant for 1 human generation.

Sorry for my „deunglisch“!