record durations, not time of day

classic Classic list List threaded Threaded
5 messages Options
Eric Beversluis Eric Beversluis
Reply | Threaded
Open this post in threaded view
|

record durations, not time of day

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: record durations, not time of day

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.

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Alan Boba Alan Boba
Reply | Threaded
Open this post in threaded view
|

Re: record durations, not time of day

In reply to this post by Eric Beversluis
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:

> 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
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>


--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: record durations, not time of day


On 1-8-2019 05:05, Alan B 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
23:59

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

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows10 / LibreOffice  (latest?)
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Fwd: [libreoffice-users] record durations, not time of day

In reply to this post by Eric Beversluis
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 <
[hidden email]>:

> 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
seconds.
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
minutes).

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.


Kind regards

Johnny Rosenberg


>
> Eric Beversluis
> Short fiction at www.ericbeversluis.com
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy