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

date timemicrosoft exceltimeworksheet-function

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

Best Answer

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.

Related Question