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

users
#1

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

#2

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. :wink:

#3

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

#4

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

#5

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.

#6

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.

#7

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

#8

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

Brian Barker

#9

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.

#10

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