Ask Your Question

Calc and Hour duration format?

asked 2021-03-02 04:56:16 +0200

Nether53d2 gravatar image

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


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

Thank you

edit retag flag offensive close merge delete


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

Earnest Al gravatar imageEarnest Al ( 2021-03-02 05:59:06 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-03-02 07:01:51 +0200 )edit

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 ...(more)

Mike Kaganski gravatar imageMike Kaganski ( 2021-03-02 08:01:10 +0200 )edit
Jim K gravatar imageJim K ( 2021-03-02 14:11:54 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-03-02 14:04:57 +0200

igorlius gravatar image


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.

image description

Hope it helps.

edit flag offensive delete link more



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 @Mike Kaganski, is usually a better choice.

keme gravatar imagekeme ( 2021-03-02 15:07:53 +0200 )edit

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.

erAck gravatar imageerAck ( 2021-03-02 15:10:07 +0200 )edit

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

igorlius gravatar imageigorlius ( 2021-03-02 19:54:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-03-02 04:56:16 +0200

Seen: 42 times

Last updated: Mar 02