Monday, February 20, 2017

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. I made some notes, and I figured I 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

1 Minutes 90 Comment: Number value of 90 is in Cell B1
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".
6 1:30 Formula: =TIME(0,B1,0). Comment: Create an actual valid =Time value.
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!

No comments: