Excel: Converting a number of minutes to hours and minutes
This is one of those, it should be easy, but it turns out to be harder to do than I initially expected so I wanted to take some notes. .. and might as well share it!
Goal: Convert a Number of Minutes into actual Hours and Minutes [H:mm], e.g. 90 into 1:30 or Hours: 1, Minutes: 30.
Hours & Minutes
A | B | C | |
1 | Minutes | 90 | Comment: Number value of 90 is in Cell B1 |
2 | |||
3 | Hours & Minutes | 2160:00 | Formula: =B1. Format: Time, [h]:mm. |
4 | WTH?! | Comment: The Value is "wacked" because "90" is not actually a valid "time". | |
5 | |||
6 | 1:30 | Formula: =TIME(0,B1,0). Comment: Create an actual valid =Time value. | |
7 | |||
8 | 30 | Ok, let's find the number of minutes after subtracting all the hours. Formula: =MID(TEXT(C7,"h:mm"),FIND(":",TEXT(C7,"h:mm"))+1,2) | |
9 | Hours: 1, Minutes: 30 | Formula: =CONCATENATE("Hours: ",TEXT(C7,"H"),", Minutes: ",MID(TEXT(C7,"h:mm"),FIND(":",TEXT(C7,"h:mm"))+1,2)) Comment: You can't just use =Text(C7,"mm") because =Text pulls the full value of 90. |
So like everything, there is probably a better way to do this. I got this to work.
If you find a better way, please share!