calculate minutes worked in the hour, by the hour

[mbagby]

I need help. My payroll system doesn't calculate the hours worked by the hour so I have to export it into excel and hoping this community can help me out.

Pretend "in punch" is column A and "out punch" is column B.

I need to calculate how many minutes were worked in the hour. So, in column A, the first punch would equal 4 minutes in 9am and 60 minutes in 10am.
The next row it would be 22 minutes in 9am and 54 minutes in 10am. Make sense?

Any help is MUCH appreciated.

In Punch	Out Punch
9:56 AM	11:00 AM
9:22 AM	10:54 AM
4:57 AM	10:12 AM
11:27 AM	2:24 PM
6:00 AM	10:36 AM
10:02 AM	2:02 PM
2:32 PM	6:02 PM
12:10 PM	3:17 PM
7:01 AM	11:39 AM
3:41 PM	6:54 PM

 

[bebo021999]

Book3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	In Punch	Out Punch		9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM
2	09:56 AM	11:00 AM		4	60	-	-	-	-	-	-	-
3	09:22 AM	10:54 AM		38	54	-	-	-	-	-	-	-
4	04:57 AM	10:12 AM		60	12	-	-	-	-	-	-	-
5	11:27 AM	02:24 PM		-	-	33	60	60	24	-	-	-
6	06:00 AM	10:36 AM		60	36	-	-	-	-	-	-	-
7	10:02 AM	02:02 PM		-	58	60	60	60	2	-	-	-
8	02:32 PM	06:02 PM		-	-	-	-	-	28	60	60	62
9	12:10 PM	03:17 PM		-	-	-	50	60	60	17	-	-
10	07:01 AM	11:39 AM		60	60	39	-	-	-	-	-	-
11	03:41 PM	06:54 PM		-	-	-	-	-	-	19	60	114
Sheet2

Cell Formulas
Range	Formula
F1:L1	F1	=E1+1/24
D2:L11	D2	=MAX(0,MIN($B2,E$1)-MAX($A2,D$1))*1440

 

[mbagby]

Wow, thank you. seriously.

 

[mbagby]

If i wanted to turn the minutes it's a decimal...how would i do that? like 60 (which is minutes) turns to 1. 30 minutes turns into .5. 15 minutes .25 and so on. You would just add to the end of the formulas "/60" correct?

 

[bebo021999]

try to *24, instead of *1440