[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [libreoffice-users] Help with formula - number conversion


At 14:05 11/07/2019 -0400, Charles Marcus wrote:
I need some help with converting a number (in a text field) to a different type. The type of number is time, and it is in the form of: hh:mm:ss I need to convert this to just minutes, rounded up, so if ss is more than 30 it adds another minute.

At 20:55 11/07/2019 +0200, and at 21:12 11/07/2019 +0200, Harvey Nimmo wrote:
You might like to try the following. Given that your value is in field A1 in the form hh:mm:ss then you can do the conversion by entering the following in another field
=HOUR(A1)*60+MINUTE(A1)+ROUND(SECOND(A1)/60,0)
It doesn't seem to matter if the value in A1 is in time format or text format. I'm sure there are buffs out there who could offer a more elegant solution, but the above seems to work all right for the cases I tried.

I'm not sure if it is more elegant, but there is a simpler solution. Time values are represented internally as numbers and fractions of days, so it is unnecessary to unpack the separate hour, minute, and second values. If you just multiply by 1440, the number of minutes in a day, you will have the total number of minutes in the period:
=Xn*1440

Your rounding request is ambiguous.

o "Rounding up" would mean that any number of seconds other than zero would cause another whole minute to be indicated. For that, choose:
=ROUNDUP(Xn*1440)

o If you want the extra minute only when the seconds value is more than half a minute, normal rounding is appropriate:
=ROUND(Xn*1440)

o But note that normal rounding - as provided by the ROUND() function - will round 30 seconds up to the next minute, as well as values over 30. If you really want exactly thirty seconds to be rounded down - a non-standard type of rounding - you will have to work around this yourself. But I guess that you probably don't mean that.

Incidentally, by mentioning the time format hh:mm:ss you imply that what you are handling are times of day, but when you convert these to minutes, they have the feel of time intervals, not times per se. After all, no-one specifies times of day just in minutes. If you have a period of time more than twenty-four hours, the hh:mm:ss format will display the time as it would appear in a later day. So 25 hours, for example, will appear as 01:00, or 1 a.m. the following day. You can display such time intervals using the alternative format code [HH]:MM:SS.

This produces a difference between the unpacking method and simple multiplication. If your time interval is 25 hours - 25:00:00 - the HOUR() function will return this as 1 hour - meaning one o'clock - whereas the multiplication method will respect the full 25 hours. Only you can know which you need or if this matters.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
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

References:
[libreoffice-users] Help with formula - number conversionTanstaafl <tanstaafl@libertytrek.org>
Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.