Excel - converting minutes to hours and minutes in hh:mm format

Is there a way in Excel to convert minutes to hours and minutes in hh:mm format. For example, convert 167 minutes to 2:47.

2 Answers

Here is one way to achieve this. Divide the minute value by 1440 (A1/1440). Format the value as "Time".

Goto: for more info.

A bit more complicated, but this will work even for minutes >= 1440 or hours >= 24. This doesn't need any special time formatting for cells either. It avoids the "AM"/"PM" as well.

=QUOTIENT(A1,60)
&":"
&IF(LEN(MOD(A1,60))=1,0,"")
&MOD(A1,60)

Example outputs:

• "5" -> "0:05"
• "20" -> "0:20"
• "60" -> "1:00"
• "740" -> "12:20"
• "1520" -> "25:20"
• "7200" -> "120:00"

Essentially, what we’re doing is first taking the quotient that yields the hour as an integer, and the concatenate that using “&” with the colon “:” and concatenating that with the minutes via the MOD([dividend],[divisor]) function.

The “IF” statement checks using LEN() that if the minutes resulted from MOD() is one digit (length 1) like 0, 1, 2, …, 7, 8, 9, not something like 20, then it will prepend a 0 before the single minute digit, so you won’t have times looking like “3:9”, so it will be “3:09” for 3 hours and 9 minutes.

Alternatively instead of “LEN(MOD())=1” to check for one digit, you can use “MOD()<=9”

It might make more sense to use the length function because whether we prepend 0 is directly based on the length.