Convert number of minutes to time in Excel hh:mm format

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 

4 Answers

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.

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

This is the output

Just format the cells to time..?

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