Time in/Out Diff

[RudRud]

Book1
	A	B	C	D	E	F
1	Time In	Time Out	Hour	Minute	Combine Hour with Min	Expected Result
2	9:00 AM	7:25 PM	1.42	85	85	1:25
3	8:25 AM	5:35 PM	0.17	10	10	0:10
4	9:00 AM	5:00 PM	-1.00	-60	-60
5	7:35 AM	7:00 PM	2.42	02:25
6	10:10 AM	7:06 PM	-0.07	-4	#VALUE!
Sheet1

Cell Formulas
Range	Formula
A2,A4	A2	=TIME(9,0,0)
B2	B2	=TIME(19,25,0)
C2:C6	C2	=MOD(B2-A2,1)*24-9
D2:D4,D6	D2	=(MOD(B2-A2,1)*24-9)*60
E2:E4	E2	=LET(Hr,MOD(B2-A2,1)*24-9, Min,(MOD(B2-A2,1)*24-9)*60, Min)
A3	A3	=TIME(8,25,0)
B3	B3	=TIME(17,35,0)
B4	B4	=TIME(17,0,0)
D5	D5	=TEXT((MOD(B5-A5,1)*24-9)*60/(24*60),"[hh]:mm")
E6	E6	=TEXT((MOD(B6-A6,1)*24-9)*60/(24*60),"[hh]:mm")

Hi, Expected Result is 1:25 and 0:10

How to convert minute/hour to Hour with Minutes

 

[Fluff]

How about

=B2-A2-9/24

and format the cell as Time.
I'm not sure what you want for E4 as you can't have -ve time

 

[RudRud]

How about

=B2-A2-9/24

and format the cell as Time.
I'm not sure what you want for E4 as you can't have -ve time

Thanks Fluff for fast response, It's working great.

E4 supposed to be -1 pm, because its undertime below 9 hours , is that possible? or need to put to another column if undertime which below 9 hours

 

[Fluff]

You could use

=LET(ot,B2-A2-9/24,IF(ot>0,ot,"-"&TEXT(ABS(ot),"[h]:mm:ss")))

but the value for -ve time will be text rather than a number

 

[RudRud]

You could use

=LET(ot,B2-A2-9/24,IF(ot>0,ot,"-"&TEXT(ABS(ot),"[h]:mm:ss")))

but the value for -ve time will be text rather than a number

Ok, Thankyou

 

[Fluff]

You're welcome & thanks for the feedback.