Formula - Calculating current headcount based on punch clock times

[gd6noob]

Hi guys,

Just a little help.

Using below table, I need to count how many people on onsite using their SOS(Start of Shift) and EOS. If they have only 1 time under SOS, then it would mean they are onsite. If they have 2 punches, 1 under SOS and 1 under EOS, then they are not onsite.
For calculating how many are on break headcount, I assume it would be almost similar formula? If they have a break out time, it means they are on break.

Green Badge Sign-In Sheet.xlsb
	C	D	E	F	G	H	I	J	K	L	M	N
2							Sunday
3							Login	SOS	Break Out	Break In	EOS	Total Hous
4							Test 1
5							Test 2
6							Test 3
7							Test 4
8							Test 5
9	Dashboard Summary						Test 6
10	Days	Headcount	Break	Total Hours			Test 7
11	Sunday						Test 8
Week 42

Cell Formulas
Range	Formula
F11	F11	=IF(SUM(N4:N28)=0,"",SUM(N4:N28))
N4:N11	N4	=IF(J4="","",IF(M4="","",IF(J4>M4,M4+1,M4)-J4))

Thanks for any help.

 

[gd6noob]

Apparently you missed the part where I expressed that concern back up in post 4:


You never addressed that concern, so I thought then maybe it was not really a concern.
But based on your last post, I am guessing it really is.
That is why when posting sample data, you want to do your best to catch all the different scenarios to make sure that they have all been addressed.

Ohh I thought I did in Post #5. My Apologies.

 

[Joe4]

Ohh I thought I did in Post #5. My Apologies.

In looking at the history of the post, it looks like you made edits to post 5 after I had already posted post 6, so I did not see them.

See if these updates work for you:

D11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,">0") + COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,"",L3:L11,"")
E11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,"")

 

[gd6noob]

In looking at the history of the post, it looks like you made edits to post 5 after I had already posted post 6, so I did not see them.

See if these updates work for you:

D11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,">0") + COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,"",L3:L11,"")
E11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,"")

Yes, after posting it originally, it did not clarify what I wanted so I edited it.

Thank very much you, this is working how I expected now.

 

[Joe4]

You are welcome.
Glad we got it all sorted and working the way you want!

 

[gd6noob]

In looking at the history of the post, it looks like you made edits to post 5 after I had already posted post 6, so I did not see them.

See if these updates work for you:

D11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,">0") + COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,"",L3:L11,"")
E11: =COUNTIFS(J3:J11,">0",M3:M11,"",K3:K11,">0",L3:L11,"")

Hi @Joe4

Just had a follow up question on this, I would like to know if someone has a start time before 18:00 in column J or after. I would like to separate this from a Day Shift and Night Shift.

ie.
in D11, if someone has start before 18:00, then it would continue with your formula.
in D12, if someone has start after 18:00, then it would continue with your formula.

I hope this makes sense.

 

[Joe4]

Hi @Joe4

Just had a follow up question on this, I would like to know if someone has a start time before 18:00 in column J or after. I would like to separate this from a Day Shift and Night Shift.

ie.
in D11, if someone has start before 18:00, then it would continue with your formula.
in D12, if someone has start after 18:00, then it would continue with your formula.

I hope this makes sense.

That is really a whole new question, and should be posted to its own new thread.