Calculate start time from end time with multiple times per day for each ID

[leapinlabs]

I have an excel spreadsheet that calculates time from the last clock out of each day and adjust the formula to look at specific ranges for each employee. What I would like to have is a formula that can determine total hours (start time from final time) for each date for each employee id. As you can see on the attached, it is not calculating correctly. Column K (Daily Time) is where I want to calculate each date for each employee, leaving blank anything that is not a final clockout. I cannot add in xl2BB so have just pasted what I have currently here.

ID

Clock In ID

Clock Out ID

In time

Out time

Hours

Daily Time

3039​

DSM

DSM

9/19/2024 7:50​

9/19/2024 8:33​

0.72​

=IF(INT(G2)=INT(H3)," ",H2-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H2))))

3039​

StuNeuharth

StuNeuharth

9/19/2024 9:02​

9/19/2024 12:23​

3.35​

=IF(INT(G3)=INT(H4)," ",H3-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H3))))

3039​

DSM

DSM

9/19/2024 12:33​

9/19/2024 15:22​

2.82​

=IF(INT(G4)=INT(H5)," ",H4-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H4))))

3039​

DSM

DSM

9/20/2024 7:17​

9/20/2024 8:44​

1.45​

3039​

StuNeuharth

StuNeuharth

9/20/2024 9:12​

9/20/2024 11:45​

2.55​

3039​

DSM

DSM

9/20/2024 11:54​

9/20/2024 13:20​

1.43​

8:24​

3040​

Alberts

Alberts

9/19/2024 6:12​

9/19/2024 15:54​

9.70​

10:53​

3040​

Alberts

Alberts

9/20/2024 6:24​

9/20/2024 15:11​

8.78​

10:15​

3145​

GPS

GPS

9/19/2024 5:15​

9/19/2024 15:42​

10.45​

10:41​

3145​

GPS

GPS

9/20/2024 5:16​

9/20/2024 14:54​

9.63​

9:58​

3161​

Firesteel Finisher 1

Firesteel Finisher 1

9/19/2024 5:36​

9/19/2024 9:38​

4.03​

3161​

TCMGR

MediaPork

9/19/2024 10:02​

9/19/2024 15:24​

5.37​

10:23​

3161​

Firesteel Finisher 1

Firesteel Finisher 1

9/20/2024 4:56​

9/20/2024 8:55​

3.98​

3161​

MediaPork

MediaPork

9/20/2024 9:21​

9/20/2024 14:53​

5.53​

9:57​

3182​

BatesWean

BatesWean

9/19/2024 5:01​

9/19/2024 10:29​

5.47​

3182​

Viola

Viola

9/19/2024 11:27​

9/19/2024 13:58​

2.52​

8:57​

3182​

BatesWean

BatesWean

9/20/2024 8:03​

9/20/2024 12:41​

4.63​

3182​

Viola

Viola

9/20/2024 13:11​

9/20/2024 15:24​

2.22​

10:28​

 

[myall_blues]

Which column is which here? For example, counting back from Column K (Daily Time) makes 'In time' column H, which doesn't align with your formula.
How do you tell if something is not a final clockout?

 

[leapinlabs]

Which column is which here? For example, counting back from Column K (Daily Time) makes 'In time' column H, which doesn't align with your formula.
How do you tell if something is not a final clockout?

 

[leapinlabs]

I'm sorry, there was a hidden column, I, column G is in time and column H is out time.

 

[leapinlabs]

I'm sorry, there was a hidden column, I, column G is in time and column H is out time.

And the way to tell if it's not a final clock out is if the next line has the same date.

 

[Vogateer]

I'm not 100% on what you're trying to do.

1. Do you want to show many hours an employee (based on ID in the Clock In ID) worked on a given day on their final clock-out for that day?
   
   
2. Do you want to include hours between a clock-out and the next clock-in for the same day? To me that seems like lunch time that you would want to exclude, or time you would not want to include, but maybe you're trying to do something different.

As it is, your formula looks across the columns for the earliest clock-in regardless of employee, and even if it looked at one employee's times, it would include that "lunch time" as part of their hours.

Please give us examples of what the values should be for those calculations, which is super helpful.

 

[leapinlabs]

Sorry for the confusion. What I'm trying to do is calculate total time from first clock in to last clock out per day per employee. The formula I have set up is looking at the final clock out for each date and subtracting the orginal clock in for same date. However, I have an issue with results because it is not looking by employee.
I need:
total time start to finish for each day for each employee. Currently I have to adjust my formulas to only look at the specific employee or it calculates other employees for that date as well.
Said another way, I need to add to my formula to only look at the "ID" and if it matches, calculate the total start to end time for each date and ignore other "ID"s.

ID

Clock In ID

Clock Out ID

In time

Out time

Hours

Daily Time

Should be:

3039​

DSM

DSM

9/19/2024 7:50​

9/19/2024 8:33​

0.72​

=IF(INT(G2)=INT(H3)," ",H2-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H2))))

3039​

StuNeuharth

StuNeuharth

9/19/2024 9:02​

9/19/2024 12:23​

3.35​

=IF(INT(G3)=INT(H4)," ",H3-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H3))))

3039​

DSM

DSM

9/19/2024 12:33​

9/19/2024 15:22​

2.82​

=IF(INT(G4)=INT(H5)," ",H4-MIN(FILTER($G$2:$G$36,INT($G$2:$G$36)=INT(H4))))

3039​

DSM

DSM

9/20/2024 7:17​

9/20/2024 8:44​

1.45​

3039​

StuNeuharth

StuNeuharth

9/20/2024 9:12​

9/20/2024 11:45​

2.55​

3039​

DSM

DSM

9/20/2024 11:54​

9/20/2024 13:20​

1.43​

8:24​

6.03​

3040​

Alberts

Alberts

9/19/2024 6:12​

9/19/2024 15:54​

9.70​

10:53​

3040​

Alberts

Alberts

9/20/2024 6:24​

9/20/2024 15:11​

8.78​

10:15​

8.87​

3145​

GPS

GPS

9/19/2024 5:15​

9/19/2024 15:42​

10.45​

10:41​

10.27​

3145​

GPS

GPS

9/20/2024 5:16​

9/20/2024 14:54​

9.63​

9:58​

9.38​

3161​

Firesteel Finisher 1

Firesteel Finisher 1

9/19/2024 5:36​

9/19/2024 9:38​

4.03​

3161​

TCMGR

MediaPork

9/19/2024 10:02​

9/19/2024 15:24​

5.37​

10:23​

9.88​

3161​

Firesteel Finisher 1

Firesteel Finisher 1

9/20/2024 4:56​

9/20/2024 8:55​

3.98​

3161​

MediaPork

MediaPork

9/20/2024 9:21​

9/20/2024 14:53​

5.53​

9:57​

9.97​

3182​

BatesWean

BatesWean

9/19/2024 5:01​

9/19/2024 10:29​

5.47​

3182​

Viola

Viola

9/19/2024 11:27​

9/19/2024 13:58​

2.52​

8:57​

8.57​

3182​

BatesWean

BatesWean

9/20/2024 8:03​

9/20/2024 12:41​

4.63​

3182​

Viola

Viola

9/20/2024 13:11​

9/20/2024 15:24​

2.22​

10:28​

7.21​

 

[Vogateer]

Which ID are you wanting to use, since the first 3 columns have IDs?

If not column 1, what are we supposed to do when you have TCMGR as the Clock-in ID on the same row as MediaPork is the Clock-out ID. Does that mean the Clock-in and clock-out on that row are for two different IDs? If so, then there's no clock-in for MediaPork on that day.

I think you want to use the very first row of IDs, based on your example data. If that's the case, there might be rounding errors for the numbers I have in my spreadsheet because they don't match exactly, but this formula appears to get close to what you want:

=LET(last_clockout, MAX(FILTER($H$2:$H$19, (INT($H$2:$H$19)=INT($H2))*($A$2:$A$19=$A2))), first_clockout, MIN(FILTER($G$2:$G$19, (INT($G$2:$G$19)=INT(last_clockout))*($A$2:$A$19=$A2))), IF(last_clockout=H2,(last_clockout-first_clockout)*24, ""))

You would need to adjust the ranges, mostly the numbers I think. I'm assuming rows 2:19 aren't enough to cover all your data.

 

[leapinlabs]

Which ID are you wanting to use, since the first 3 columns have IDs?

If not column 1, what are we supposed to do when you have TCMGR as the Clock-in ID on the same row as MediaPork is the Clock-out ID. Does that mean the Clock-in and clock-out on that row are for two different IDs? If so, then there's no clock-in for MediaPork on that day.

I think you want to use the very first row of IDs, based on your example data. If that's the case, there might be rounding errors for the numbers I have in my spreadsheet because they don't match exactly, but this formula appears to get close to what you want:

=LET(last_clockout, MAX(FILTER($H$2:$H$19, (INT($H$2:$H$19)=INT($H2))*($A$2:$A$19=$A2))), first_clockout, MIN(FILTER($G$2:$G$19, (INT($G$2:$G$19)=INT(last_clockout))*($A$2:$A$19=$A2))), IF(last_clockout=H2,(last_clockout-first_clockout)*24, ""))

You would need to adjust the ranges, mostly the numbers I think. I'm assuming rows 2:19 aren't enough to cover all your data.

Yes, column 1 is the ID I want to use.
I'm going to give this formula a whirl and see where it gets me! Thanks so much!

I just tested this formula and with very minor (range) modification, it works beautifully! Thank you so much for your help with this!!!!!!!!

 

[Vogateer]

Glad to hear it worked!

If you wouldn't mind marking the solution as the post with the formula in it, that should help others find the solution. I think that's how the moderators want us to do it.