record durations, not time of day

users
#1

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?

Eric Beversluis
Short fiction at www.ericbeversluis.com

#2

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.

Some suggestions:

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
=VALUE("0:"&A1)
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.)

I trust this helps.

Brian Barker

#3

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

#4

When using square brackets in hours ('[HH]')  the time does not stop at 23:59

25:00 with format [HH]:MM will show 25:00

when using format HH:MM, it will show 01:00

#5

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: