[Calc] Good way to turn hours+minutes into minutes?

classic Classic list List threaded Threaded
10 messages Options
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

[Calc] Good way to turn hours+minutes into minutes?

Hello,

I have a spreadsheet where time is formatted as HHhMM.

In the next column, I'd like to show this in minutes. What would be a good
way to achieve this?

Thank you.

https://i.postimg.cc/qRKCK14F/image.png



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

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

James Knott-2 James Knott-2
Reply | Threaded
Open this post in threaded view
|

Re: [Calc] Good way to turn hours+minutes into minutes?

On 7/17/20 8:37 AM, Gilles wrote:
> I have a spreadsheet where time is formatted as HHhMM.
>
> In the next column, I'd like to show this in minutes. What would be a good
> way to achieve this?

Many years ago, when copying records to  cassettes (remember them?), I
used the decimal degree to degree, minute, second conversion function on
my calculator to do that.  Perhaps that would work for you.  In Calc of
course, not with a calculator. ;-)

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

Re: [Calc] Good way to turn hours+minutes into minutes?

On 7/17/20 8:56 AM, James Knott wrote:

> On 7/17/20 8:37 AM, Gilles wrote:
>> I have a spreadsheet where time is formatted as HHhMM.
>>
>> In the next column, I'd like to show this in minutes. What would be a
>> good
>> way to achieve this?
>
> Many years ago, when copying records to  cassettes (remember them?), I
> used the decimal degree to degree, minute, second conversion function
> on my calculator to do that.  Perhaps that would work for you.  In
> Calc of course, not with a calculator. ;-)

I don't see that particular function in Calc, but here's a way to
accomplish the same thing:
https://www.calculatorsoup.com/calculators/conversions/convert-decimal-degrees-to-degrees-minutes-seconds.php

--
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: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Gilles
At 05:37 17/07/2020 -0700, Gilles Noname wrote:
>I have a spreadsheet where time is formatted as HHhMM. In the next
>column, I'd like to show this in minutes. What would be a good way
>to achieve this?

That depends on exactly what you have in your cells. Do you mean that
you have text values or that you have numerical values formatted as,
say, HH\hMM ? Partly because your image shows the values left
aligned, I'm guessing that you have text values. If so, use
=LEFT(Xn;2)*60+RIGHT(Xn;2)

If there is a risk that cell values might include trailing spaces you could use
=LEFT(Xn;2)*60+MID(Xn;4;2)
or
=LEFT(Xn;2)*60+RIGHT(TRIM(Xn);2)

Note that the LEFT() and RIGHT() functions return text values, but
these are implicitly converted to numerical values on the fly by the
need to apply the "+" operator in the formulae.

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

Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Gilles

On 17-7-2020 14:37, Gilles wrote:

> Hello,
>
> I have a spreadsheet where time is formatted as HHhMM.
>
> In the next column, I'd like to show this in minutes. What would be a good
> way to achieve this?
>
> Thank you.
>
> https://i.postimg.cc/qRKCK14F/image.png
>
>
>
> --
> Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html


if A2 = "01h14"

Then the formula: =TIMEVALUE(REPLACE(A2;3;1;":"))*24*60

Will return: 74


Basically first replace 'h' for an ':'

timevalue returns the minutes since midnight.


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

Re: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Brian Barker
Brian Barker wrote
> I'm guessing that you have text values. If so, use
> =LEFT(Xn;2)*60+RIGHT(Xn;2)

That was the easiest solution:

=LEFT(F3;2)*60+RIGHT(F3;2)

Next, copy the cell (formula), select all the cells below, and paste.

But before, make sure the destination column is also set to Text, like the
source column.

Thank you.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
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: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Luuk
At 15:50 17/07/2020 +0200, Luuk Noname wrote:
>if A2 = "01h14"
>Then the formula: =TIMEVALUE(REPLACE(A2;3;1;":"))*24*60
>Will return: 74
>
>Basically first replace 'h' for an ':'
>timevalue returns the minutes since midnight.

Except that this fails for time values over twenty-four hours.

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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Gilles
At 06:54 17/07/2020 -0700, Gilles Noname wrote:
>Brian Barker wrote
>>I'm guessing that you have text values. If so, use
>>=LEFT(Xn;2)*60+RIGHT(Xn;2)
>
>That was the easiest solution:
>=LEFT(F3;2)*60+RIGHT(F3;2)
>
>Next, copy the cell (formula), select all the cells below, and paste.

Or use the "fill handle" to drag down a column.

>But before, make sure the destination column is also set to Text,
>like the source column.

I don't know why you think this is necessary or desirable. The result
of the formula is a number, and this is not changed by formatting the
cells as Text. If you want the values left aligned you can arrange
that separately. You can anyway format cells containing numbers
however you wish. If you really want text values, you can wrap the
original formula in the TEXT() function.

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

remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Luuk
Hi,
If it's just showing, using a numbers format defined as [m] ("m"
between square brackets) will do the job. Since it is time, there
should be no decimal minutes to show and this format should work out.
Using this format will also allow you to continue making operations
with the time values without having to think about the units of what
you are manipulating. And if you want to show seconds, [s] is the
format to use.
As a side note, the "time" format of Calc is days (the value 1 is
equivalent to 24 hours), so multiplying the time value by 24 will give
you decimal hours, multiplying the time value by 24*60 will give
minutes, and  by 24*60*60  will give seconds. Once you have the number
you can use standard decimal number formats will display whatever units
you want to use with the number of decimal places you are looking for.
You can also go the other way: to encode, say, 7 hours and 41 minutes,
use this formula: =(7+41/60)/24.
I hope this helps.
Rémy.
Le vendredi 17 juillet 2020 à 15:50 +0200, Luuk a écrit :

> On 17-7-2020 14:37, Gilles wrote:
> > Hello,
> > I have a spreadsheet where time is formatted as HHhMM.
> > In the next column, I'd like to show this in minutes. What would be
> > a goodway to achieve this?
> > Thank you.
> > https://i.postimg.cc/qRKCK14F/image.png
> >
> >
> >
> > --Sent from:
> > http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>
> if A2 = "01h14"
> Then the formula: =TIMEVALUE(REPLACE(A2;3;1;":"))*24*60
> Will return: 74
>
> Basically first replace 'h' for an ':'
> timevalue returns the minutes since midnight.
>

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

Re: [Calc] Good way to turn hours+minutes into minutes?

In reply to this post by Gilles
Den fre 17 juli 2020 kl 14:39 skrev Gilles <[hidden email]>:

> Hello,
>
> I have a spreadsheet where time is formatted as HHhMM.
>
> In the next column, I'd like to show this in minutes. What would be a good
> way to achieve this?
>
> Thank you.
>

The quickest way I can think of is to just use the same value and format it
to show minutes only. An example:
A1
Input: 12:34:56
Format: HH:MM
Result: 12:34

B1:
Formula: =A1
Format: [MM]
Result: 754

So same value, but formatted as [MM]. Don't forget the [].


This won't work if the values are in text format, of course.




Kind regards

Johnny Rosenberg



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