Trying to make a time tool

[bh24524]

Can anyone tell me how I might do the below multiple part scenario:

I need to subtract an end time and a start time to get the hours and the minutes. Start time is in P2 and end time is in Q2. I wanted to do this in a way that I actually don't need to enter the colon and could just enter the time as a straight number and it automatically put the colon in there. To do that, I formatted the cells with the start and end times with a custom format of "00\:00"

here's where it gets a bit tricky. I need another cell to show the difference between the two times in decimal format. That would be cell Q3. Right now I have Q3 showing the hours and minutes so for example, in P2. I have 11:12 and in Q2 I have 12:17. Q3 shows 1:05.

So for that 1 hour and 5 minutes, is there a way I can get that to show as a decimal ultimately reading 1.08? (yes 8 not 5 I had divided the 5 mins by 60)

 

[FormR]

I wanted to do this in a way that I actually don't need to enter the colon and could just enter the time as a straight number and it automatically put the colon in there. To do that, I formatted the cells with the start and end times with a custom format of "00\:00"

Hi, in my opinion this is a bad idea - you end up jumping through hoops whenever you want to perform calculations on those times - that said, here is a formula you can try.

Excel 2013/2016
	P	Q
1	Start	End
2	1112	1217
3		1.083333
Sheet1

Cell Formulas
Range	Formula
Q3	=(TEXT(Q2,"00\:00")-TEXT(P2,"00\:00"))*24

 

[bh24524]

Thank you for your help on that. That should give me what I need.