Calc and Hour duration format?

[Nether53d2]

I am so confused by the format Calc is expecting. I am former Numbers user(Apples version of Calc)
what am trying to achieve is to regard a cell that has a single digit as an hour and append “H” to the end.
For example, 3 will be reformatted to 3 H
Right now I am getting some weird calculations. I’ve tried applying
HH" H" to cell that has a single digit, it turns the cell into 00 H

HH" H"  22:33:22     22 H
HH" H"  221122       00 H
HH" H"  222          00 H
HH" H"  2            00 H

It keeps inserting 000s into the cell, the only one that gives me some result is the first one.
but having to type

22:33:22

for every cell is not ideal. Instead how can I get calc to just regard any digit as an hour?

Thank you

[EarnestAl]

You don’t want parts of an hour? Try format [HH] "H"

[JohnSUN]

I don’t know how Numbers handled data with the letter H, but you probably already guessed that Calc is a slightly different program, right? Everything would be much easier if there were 100 seconds in a minute, 100 minutes in an hour, and 100 hours in a day. While this is not the case, leave the values as you enter, and for further calculations use the TIME(your_value;0;0) function or divide your number by 24.

[mikekaganski]

The question is what you are trying to do with the cell. If your idea is to put any number there, and have it displayed as “<this_number> H”, then it’s as easy as using 0" H" format string (but note that it would round any fraction, not truncate, so putting 2.6 into the cell, you will have 3 H). But then you have not a time in your cell, just a number formatted with a suffix. If you want to use the cell further in time calculations, you would either need to account for this, and divide the rounded value by 24; or you would need to do the correct thing from the start, and put time into the cell, i.e. writing e.g. 2:00, not 2.

The zeroes that you correctly get when format a cell containing a whole number as time are because times in Calc are fractions of a day, 1 being one day (24 hours), which is displayed as 0:00 (midnight) when using wall clock format.

[jimk]

Also posted at libreoffice - Libre office Calc and Hour duration format? - Stack Overflow.

[BigRAl]

Also posted at Calc and Hour duration format? : libreoffice

[igorlius]

Hi,

not sure if this is what you are trying to do,
but if you just want to append the " H" you can use

@" H"

This will change the “Display” but you can still use the Cell for calculations with other Cells.

Hope it helps.

[keme]

ALERT

Using the @ placeholder will force text input, which in calculations is handled differently to what you may expect. Arithmetic operators will force numeric interpretation of the text. Functions expecting number will disregard numeric text.

This means, based on the animated demo:

• The formula =C2+0 will return 23 (forced conversion)
• The formula =SUM(C2) will return 0 (text was disregarded)

format string 0" H", as suggested in a comment from @mikekaganski, is usually a better choice.

[erAck]

You can’t use that directly for time calculations with other (“real”) time cells though, because the value 1 is 24 hours (1 day). See Mike’s comment under the question.

[igorlius]

My bad, the 0" H" is indeed the better / correct option.