1

I have a set of data that is represented in minutes. This data needs to be converted to hours and the remainder to minutes.

For example, in column A I have 120 and in Column B I need to present the data in the hh:mm format. I have tried this: =A1/60 which gives me 2 as a result in column B, indicating that 120 minutes is 2 hours.

But for a record that contains data such as 179 minutes, this gets converted to 2.98333 hours after my A1/60 formula, so this approach does not work. The result I need is hours and minutes: 02:59

How can I convert my minutes entries to display in the hh:mm format?

What I've tried:

60 - because there are 60 minutes in 1 hour

=a1/60 

Minutes                 A/60 result        hh:mm needed      
A                        B                     C
120                      2                   02:00
179                      2.98333             02:59
Improve this question
3
  • Mark, you are right. I meant to say 120 minutes not seconds
    – Ana
    Commented Sep 28, 2017 at 5:49
  • I did a little cleanup in your question. One thing that isn't clear though because the question describes it both ways: do you need just a display format of hh:mm or do you need the hours and minutes as separate, usable values? If you need just the display, my solution will do that. If you require separate values, you will need Prasanna's solution.
    – fixer1234
    Commented Sep 28, 2017 at 8:09
  • @mark You are close. Because of how excel stores time values, you need to divide by the number of minutes in a day, not an hour. Divide minutes by 1440, then format as time h:mm
    – Ron Rosenfeld
    Commented Sep 29, 2017 at 1:56

4 Answers 4

Reset to default
4

I tested this in LO Calc, so verify that it works the same in Excel.

You can do what you describe in the question with formatting if you convert the minutes to Excel time using the TIME() function. The TIME function requires input arguments of hours, minutes, and seconds, and converts that to the form Excel uses internally (fraction of a day), which can then be formatted. At least in LO Calc, the minutes entry is not limited to 60; you can put in any quantity.

So the formula in B2 would be =TIME(0,A2,0)

That's 0 hours, the number of minutes in A2, and 0 seconds.

Format the cell as the time format you want (HH:MM).

For example, your first entry of 179 minutes produces an output of 02:59.

Improve this answer
2
  • 2
    +1 for TIME() function. Works in Excel as well !
    – Prasanna
    Commented Sep 28, 2017 at 8:17
  • 1
    better solution in my opinion. Thanks!
    – Jared Beach
    Commented Jan 4, 2020 at 16:41
2

If column A contains the time (in minutes) you can have time in hours in column B and remaining minutes in column C.

  • In column B use the formula: =(A2-C2)/60
  • In column C use the formula: =MOD(A2,60)

This is how it looks with formulas in

Table with formulas

This is the output

Output

Improve this answer
1
  • +1 for MOD function. If only we knew what Mark needs... :-)
    – fixer1234
    Commented Sep 28, 2017 at 8:23
0

enter image description here

Just format the cells to time..?

Improve this answer
1
  • Formatting the time only works if the number is stored as "Excel" time (fraction of a day). The OP indicates that the numbers are seconds, so they would need to be converted first.
    – fixer1234
    Commented Sep 28, 2017 at 5:43
0

If you are in the odd situation (like I was) where you may have to deal with negative times (minutes over/under a certain benchmark) then neither of these methods will work.

The Time function errors when using negative minutes.

The helper columns/rows method doesn't work as the formulas interact oddly with negative numbers.

So this is what I did:

  • I still used helper rows/columns, but for the hours I just rounded down the number of minutes divided by 60: =rounddown(L9/60,0) [This results in a negative number for hours]
  • For the minutes, I multiplied the number of hours by 60, and subtracted the total number of minutes from the number of hours (represented in minutes) [basically doing a MOD calculation on it]. Then I took the absolute value in order to get a positive number: =abs(L9-(60*L11))
  • Then for a nice h:mm display, I concatenated the results padding the minutes to two digits: =CONCATENATE(L11,":",TEXT(L12,"00"))

It looks like this with the raw values: Output with values

And looks like this with the formulas: Spreadsheet with formulas

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .