Help with formula - number conversion

classic Classic list List threaded Threaded
8 messages Options
Tanstaafl Tanstaafl
Reply | Threaded
Open this post in threaded view
|

Help with formula - number conversion

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!

--
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
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Help with formula - number conversion

On Thu, 2019-07-11 at 14:05 -0400, Tanstaafl wrote:

> 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!

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




--
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
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Help with formula - number conversion

On Thu, 2019-07-11 at 20:55 +0200, Harvey Nimmo wrote:

> On Thu, 2019-07-11 at 14:05 -0400, Tanstaafl wrote:
> > 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!
>
> 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

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



--
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
Tanstaafl Tanstaafl
Reply | Threaded
Open this post in threaded view
|

Re: Help with formula - number conversion

On Thu Jul 11 2019 15:12:05 GMT-0400 (Eastern Standard Time), Harvey
Nimmo <[hidden email]> wrote:
> 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.

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

--
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: Help with formula - number conversion

In reply to this post by Tanstaafl
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: [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

Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Help with formula - number conversion

In reply to this post by Tanstaafl
On Fri, 2019-07-12 at 13:00 -0400, Tanstaafl wrote:

> On Thu Jul 11 2019 15:12:05 GMT-0400 (Eastern Standard Time), Harvey
> Nimmo <[hidden email]> wrote:
> > 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.
>
> awesome! Thanks very much Harvey! Worked like a charm...

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






--
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
scott-3 scott-3
Reply | Threaded
Open this post in threaded view
|

RE: Help with formula - number conversion

In reply to this post by Harvey Nimmo
I have unsubscribed numerous times. Stop sending me your junk!

-----Original Message-----
From: Harvey Nimmo <[hidden email]>
Sent: Thursday, July 11, 2019 1:55 PM
To: [hidden email]
Subject: Re: [libreoffice-users] Help with formula - number conversion

On Thu, 2019-07-11 at 14:05 -0400, Tanstaafl wrote:

> 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!

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




--
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
libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: Help with formula - number conversion

[hidden email] 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
<[hidden email]>. 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/>

--
Mark


--
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