Help with formula - number conversion

users
#1

Hi everyone,

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.

I'm at a loss as to how to even start (I'm not a spreadsheet guy)...

I would appreciate any pointers on how to get this done...

Thanks!

#2

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(SEC(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.

Cheers
Harvey

#3

Sorry, Tanstaafl!

There is a slight error in the above. The formula should read
=HOUR(A1)*60+MINUTE(A1)+ROUND(SECOND(A1)/60,0)

'SEC' is the secant function. Also 'MIN' would also be wrong since it
calculates the minimum of a set of values.

Cheers
Harvey

#4

awesome! Thanks very much Harvey! Worked like a charm...

#5

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

#6

You're welcome. But Brian Barker's suggestion is both more elegant and
simpler, because it considers takes account of times greater than 24
hours.

Say your time is in cell A1 in text format (e.g. '25:01:50)

Then adding the formula to another cell
=ROUND(a1*1440,0) or just ROUND(a1*1440) gives the right answer (1502),
whereas my previous formula would wrongly return 1 (hour) instead of
25, giving total 62.

Cheers
Harvey

#7

I have unsubscribed numerous times. Stop sending me your junk!

#8

scott@valuedinsights.com wrote:

I have unsubscribed numerous times. Stop sending me your junk!

Most of us receiving mail from this list are just other users, like you, and have no ability to remove you from the list. You should be able to remove yourself by sending an email to <users+unsubscribe@global.libreoffice.org>. Make sure you send the email from the address which is subscribed to the list. You should receive an email asking you to confirm that you want to unsubscribe, to which you need to reply to complete the unsubscribe. If you don't receive that message, check if it's ended up in your junk/spam folder.

If you still can't unsubscribe, there are further troubleshooting steps at:
<https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/>