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