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