Re: [libreoffice-users] [Calc] Good way to turn hours+minutes into minutes?
Den fre 17 juli 2020 kl 14:39 skrev Gilles : > 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 > > 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: users+unsubscr...@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 > > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
Re: [libreoffice-users] [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
Re: [libreoffice-users] Re: [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
Re: [libreoffice-users] [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
[libreoffice-users] Re: [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
Re: [libreoffice-users] [Calc] Good way to turn hours+minutes into minutes?
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: users+unsubscr...@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
Re: [libreoffice-users] [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: users+unsubscr...@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
Re: [libreoffice-users] [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: users+unsubscr...@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
[libreoffice-users] [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: users+unsubscr...@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
[libreoffice-users] Re: Default font in draw
The font is defined in the style being used. Use 'view/Styles' (F11) to see and/or change the style. - Windows10 / LibreOffice (latest?) -- Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Default font in draw
Can I set the default font size and font in draw? If so, how? Thanks in advance, -bill -- Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html -- To unsubscribe e-mail to: users+unsubscr...@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
