Calculating total hours by day with multiple clocks in and out per day.

[leapinlabs]

I have a spreadsheet that includes an In Time column and an Out Time column. Each employee has several in punches and several out punches during any given date. I need to calculate the final out punch for the date less the first in punch for the same date. I've searched here an not found anything that works. Currently I am manually changing the Daily Total to

=TEXT(H3-G2,"hh:mm") to get the 7:14 . Is there a way to have excel "see" the change in the date and calculate automatically? This is my first time posting and wasn't sure how to show this...

In time	Out time	Hours	Daily Total
3/13/2023 8:17​	3/13/2023 10:54​	2.62​
3/13/2023 11:09​	3/13/2023 15:31​	4.37​	07:14
3/14/2023 5:55​	3/14/2023 9:05​	3.17​
3/14/2023 9:18​	3/14/2023 12:42​	3.4​
3/14/2023 13:15​	3/14/2023 16:03​	2.8​	10:08
3/15/2023 8:11​	3/15/2023 11:22​	3.18​
3/15/2023 11:35​	3/15/2023 12:24​	0.82​
3/15/2023 12:45​	3/15/2023 14:46​	2.02​
3/15/2023 14:58​	3/15/2023 16:30​	1.53​	08:19

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[leapinlabs]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have updated it, I am using ms 365

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D
1	In time	Out time	Hours	Daily Total
2	13/03/2023 08:17	13/03/2023 10:54	2.62
3	13/03/2023 11:09	13/03/2023 15:31	4.37	07:14:00
4	14/03/2023 05:55	14/03/2023 09:05	3.17
5	14/03/2023 09:18	14/03/2023 12:42	3.4
6	14/03/2023 13:15	14/03/2023 16:03	2.8	10:08:00
7	15/03/2023 08:11	15/03/2023 11:22	3.18
8	15/03/2023 11:35	15/03/2023 12:24	0.82
9	15/03/2023 12:45	15/03/2023 14:46	2.02
10	15/03/2023 14:58	15/03/2023 16:30	1.53	08:19:00
11
12
TravelCosts

Cell Formulas
Range	Formula
D2:D10	D2	=IF(INT(A2)=INT(B3),"",B2-MIN(FILTER($A$2:$A$100,INT($A$2:$A$100)=INT(B2))))

 

[leapinlabs]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D
1	In time	Out time	Hours	Daily Total
2	13/03/2023 08:17	13/03/2023 10:54	2.62
3	13/03/2023 11:09	13/03/2023 15:31	4.37	07:14:00
4	14/03/2023 05:55	14/03/2023 09:05	3.17
5	14/03/2023 09:18	14/03/2023 12:42	3.4
6	14/03/2023 13:15	14/03/2023 16:03	2.8	10:08:00
7	15/03/2023 08:11	15/03/2023 11:22	3.18
8	15/03/2023 11:35	15/03/2023 12:24	0.82
9	15/03/2023 12:45	15/03/2023 14:46	2.02
10	15/03/2023 14:58	15/03/2023 16:30	1.53	08:19:00
11
12
TravelCosts

Cell Formulas
Range	Formula
D2:D10	D2	=IF(INT(A2)=INT(B3),"",B2-MIN(FILTER($A$2:$A$100,INT($A$2:$A$100)=INT(B2))))

Thank you Fluff! I am not getting the correct values after copying the formula and changing column. I will look a bit deeper at this formula to try to find out why.

 

[leapinlabs]

Thank you Fluff! I am not getting the correct values after copying the formula and changing column. I will look a bit deeper at this formula to try to find out why.

I'm still struggling with the correct values.
I don't know why it's not calculating incorrectly in my spreadsheet where the formula in e3 is:
=IF(INT(A3)=INT(B4),"",B3-MIN(FILTER($A$2:$A$100,INT($A$2:$A$100)=INT(B3))))

In time	Out time	Out Punch Type	Hours	Daily Total
3/13/2023 8:17​	3/13/2023 10:54​		2.62​
3/13/2023 11:09​	3/13/2023 15:31​		4.37​	9:21:00​
3/14/2023 5:55​	3/14/2023 9:05​		3.17​
3/14/2023 9:18​	3/14/2023 12:42​		3.4​
3/14/2023 13:15​	3/14/2023 16:03​		2.8​	10:18:00​
3/15/2023 8:11​	3/15/2023 11:22​		3.18​
3/15/2023 11:35​	3/15/2023 12:24​		0.82​
3/15/2023 12:45​	3/15/2023 14:46​		2.02​
3/15/2023 14:58​	3/15/2023 16:30​		1.53​	11:33:00​

 

[Fluff]

Do you have dates for the 13th further down the data?

 

[leapinlabs]

Do you have dates for the 13th further down the data?

Ah, YES! with a different employee! So I will need to do another if to verify employee! Let me try that!

 

[leapinlabs]

Ah, YES! with a different employee! So I will need to do another if to verify employee! Let me try that!

Thank you Fluff!! That was it! A duh moment, I didn't even think of that major/minor detail! I appreciate your help with this! This is going to make life much simpler!

 

[Fluff]

Glad to help & thanks for the feedback.