I have tried everything I have come up with on searches and it’s not working. In cell I25, I want to display hours expressed as a decimal number, such as 2.38 hours, to hours:minutes, such as 2:xx. Whatever the result is… it’s about 2 hrs 33 min in this example. FWIW, cell I25’s formula is G7/I24. Currently, as a decimal number, it’s 2.38. I can’t find any format such as h:mm in the program and have tried inserting various custom formats that I have read about but nothing has worked. I get weird numbers. The Calc version is 25.2.5.2, Windows 11.
An answer can be found in this Ask Libreoffice post -
EDIT: I created a new spreadsheet.
Cell A1 = “2.75”
Cell B1 = “=A1”
I did a Format Cell of Cell B1 as HH:MM as member fpy suggests below.
It displays 22:00
Why doesn’t it display “2:45” ?
=====
Prior post…
G27 = “7.1”
I24 = “3”
I25 = “=G27/I24”
FPY… I already tried your method. It does not display as 2:33 (2 hours 33 minutes) or thereabouts, which is what I would expect. It displays it as 08:49. Yes, I included the Format Code of HH:MM.
Using your example, if I type 2.75 in cell G27, in I25, it displays 22:00, not 2:45
What have I done wrong?
Here is the new test spreadsheet that displays improperly. I can’t a difference between it and yours.
That is, if I can figure out how to upload a file.
test for hours and minutes.ods (8.9 KB)
Thanks for the upload info, I edited the post before this to contain the test file.
What you experience is a consequence of a very old mistake:
Formats specially created to display TimeOfDay (TOD) values in a traditional way (using 2 levels of sexagesimal subdivisions of the hour) can be misused to “communicate” durations…
- This requires to accept the day as a kind of mandatory unit.
- Used the ordinary way it suppresses multiples of 24 hours which is often contained in such values representing the date as an integer number.
- A modified format like one with the code
[HH]:MMhelps to increase confusion: It shows the value as if it’s given basically in hours: Technically a value allowing such a formatting is still a value in days.
If you actually want to get the number of hours for a duration (as a decimal fraction) you need to multiply the given Date-TOD value with 24, the number of hours per day.
Ok, I got it fixed, thanks. And thanks to all repliers, including fpy, I see what I did wrong. Sometimes I do stupid things. Somewhere along the way, I did utilize the 24 consideration but I think I multiplied instead of divided. Maybe. I get confused lol. Old fart. In the sheet uploaded by fpy, I failed to see the /24 part.