At 18:44 31/07/2019 -0400, Eric Beversluis wrote:
>I want to enter numbers in Calc like 3:05 that stands for 3 minutes
>and 5 seconds (duration), not for some time of day. How do I do this?
If you format your cells as Text, there is nothing to stop you
entering 3:05 just as you say, and then to look at it and know it
means three minutes and five seconds to you! Anything can stand for
anything you want. But perhaps you want to enter values like this and
have them internally represented in such a way that they can be
calculated as time intervals in the normal way.
There are two issues here. One is the cell formatting to show a time
value as you wish. That's easy: use [M]:SS. But you are also asking
to modify the automatic recognition rules that edit what you type, so
that 3:05 is edited to three minutes and five seconds. I don't see
that you can do this: you would, in any case, be suppressing the
usual form of editing to three hours and five minutes.
o Format a column as [M]:SS. Enter your values as 0:3:05 instead.
o Format a column (say A) as text. Enter your values as 3:05, as you
desire. Format another column (say B) as [M]:SS. In the second column, enter
and fill this down the column. If you don't want the first column to
show, you can hide it after data entry or tuck it away, perhaps on
another sheet. If you don't want it to print, you can exclude it from
a Print Range.
o As the previous technique, but instead of calculating values in
another column, just use the VALUE() formula within the calculations
you need to make on the values. (This will not be suitable in all cases.)
If entered just as you've typed, it seems all the formats presume hrs,
minutes, and seconds. Applying the time formats don't cause the display you
wish because the "3" is presumed hours and the "5", minutes.
Enter instead 0:3:5
Then this custom format should do:
MM:SS displays 03:05
Available ready made formats display as follows...
[HH]:MM:SS.00 displays 00:03:05.00
MM:SS.00 displays 03:05.00
[HH]:MM:SS displays 00:03:05
HH:MM:SS displays 00:03:05
HH:MM displays 00:03
On Wed, Jul 31, 2019 at 6:45 PM Eric Beversluis <
[hidden email]> wrote:
> If entered just as you've typed, it seems all the formats presume hrs,
> minutes, and seconds. Applying the time formats don't cause the display you
> wish because the "3" is presumed hours and the "5", minutes.
> Enter instead 0:3:5
> Then this custom format should do:
> MM:SS displays 03:05
> Available ready made formats display as follows...
> [HH]:MM:SS.00 displays 00:03:05.00
> MM:SS.00 displays 03:05.00
> [HH]:MM:SS displays 00:03:05
> HH:MM:SS displays 00:03:05
> HH:MM displays 00:03
When using square brackets in hours ('[HH]') the time does not stop at
I realise that I accidentally (and yet again) sent my answer directly to
the original poster. Sorry for that. Here it is, even though the question
has already been answered:
---------- Forwarded message ---------
Från: Johnny Rosenberg <[hidden email]>
Date: tors 1 aug. 2019 kl 01:30
Subject: Re: [libreoffice-users] record durations, not time of day
To: Eric Beversluis <[hidden email]>
Den tors 1 aug. 2019 kl 00:46 skrev Eric Beversluis <
> I want to enter numbers in Calc like 3:05 that stands for 3 minutes and 5
> seconds (duration), not for some time of day. How do I do this?
You do just that. For 3 minutes 5 seconds, enter 0:3:5 (or 0:03:05). the
cell value will then be (3·60+5)/86400, which is your time in days, but you
format it as MM:SS and your cell will display 03:05, or M:SS for 3:05.
If you add times and you don't want to use hours, then format as [MM]:SS.
That will give you, for instance, 65:37 for 1 hour, 5 minutes and 37
So it's basically all about formatting. The actual value in the cell is
still in days as a floating point number (for instance 0.125=4 hours=240
It's important to input hours even if you don't use them, otherwise your
input will be interpreted as HH:MM rather than MM:SS.