display set value based on location of item in counted range

[chriswrcg]

This is a weird one.
I have a list of people and there registered clock times by date. It shows when they clocked in, out for break, in for break and so on through out the day. The issue is not everyone does it the same. Some shifts only take one break while others take 2. Some people just have clock in and out and lunch.

I need to count the number of "punches" a person has in a day (easy) but then based on how many punches there are to assign each punch a label.
8 punches means clock in and out, two breaks and a lunch
6 punches means clock in and out, one break and a lunch
4 punches means clock in and out, and a lunch
2 punches means just clock in and out.

I have provided a small example but I am totally lost on how to even begin with this one. Might not be possible so I am open to other suggestions on punch distinction.

column E is an example of my desired end result.

After thinking about it, it might be possible to label each cell in the counted range based on min-max value.

[Bo_Ry]

Please try

=IF(COUNTIFS(B$2:B2,B2)=1,"In",IF(COUNTIFS(B$2:B$99,B2)-COUNTIFS(B$2:B2,B2),LOOKUP(ABS(COUNTIFS(B$2:B$99,B2)/2-COUNTIFS(B$2:B2,B2)+0.5+(COUNTIFS(B$2:B$99,B2)=6)),{0,1},{"Lunch","Break"}),"Out"))

[FlameRetired]

Edited:

Suppose you used a lookup table.

And then this formula in cell E2 and copied down.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H
1	Name	Date	Time
2	COLLINS, ADRIAN	1/8/2022	4:57:00		In		4:57:00	In
3	COLLINS, ADRIAN	1/8/2022	9:00:00		Break		9:00:00	Break
4	COLLINS, ADRIAN	1/8/2022	9:15:00		Break		11:30:00	Lunch
5	COLLINS, ADRIAN	1/8/2022	11:30:00		Lunch		15:00:00	Break
6	COLLINS, ADRIAN	1/8/2022	11:58:00		Lunch		17:00:00	Out
7	COLLINS, ADRIAN	1/8/2022	15:00:00		Break
8	COLLINS, ADRIAN	1/8/2022	15:14:00		Break
9	COLLINS, ADRIAN	1/8/2022	17:00:00		Out
10	COLLINS, ADRIAN	1/10/2022	4:57:00		In
11	COLLINS, ADRIAN	1/10/2022	9:00:00		Break
12	COLLINS, ADRIAN	1/10/2022	9:13:00		Break
13	COLLINS, ADRIAN	1/10/2022	11:45:00		Lunch
14	COLLINS, ADRIAN	1/10/2022	12:10:00		Lunch
15	COLLINS, ADRIAN	1/10/2022	17:00:00		Out

[nick.williams]

The main thing for me would be how do you differentiate between Lunch and Break? e.g. are they at defined times? Is a shift always completed in the same day?

This will label the first punch of the day as "In" and the last one as "Out", with everything else as "Break":

=IF(ROW(C2)=MIN(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"In",IF(ROW(C2)=MAX(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"Out","Break"))

For example if you want any break between 1100 and 1300 to be lunch then this works:

=IF(ROW(C2)=MIN(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"In",IF(ROW(C2)=MAX(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"Out",IF(AND(C2>=TIME(11,0,0),C2<=TIME(13,0,0)),"Lunch","Break")))

This reproduces your example results but depending on the above might not work for all scenarios.

[chriswrcg]

The problem is there is no "stability" to the shifts. There are 4 shifts between two departments, all have different schedules and needs. Some shifts only work 8 to 10 hours so they get one break and a lunch. Some shifts work 12 hours so they get two breaks and a lunch. Even though each shift does have a "set time" to take either a break or take lunch that is not always set in stone. So we would not only have to take into account how many punches a person has for a particular day, we would also have to add a shift variable.

The easiest way to distinguish between a break and lunch obviously is time. A break is 15 min where lunch is 30. That would add yet another variable to the formula to distinguish which is break and which is lunch based on the clock out length.

[chriswrcg]

Can someone explaine this to me. The formula that Bo_Ry provided works great until I change part of it. In for formula, it reference C$2:C$99 but all data below C99 does not return the proper responce. So I went in and changed it to C$2:C$45179 which is the bottom of my data in the column. It throws the results all off. Why does changing the range of part of the formula have such a dramatic effect? In the example there is a normal data set with the working formula and another data set with the changed formula that is no longer working.

[chriswrcg]

Can someone explain this to me. The formula that Bo_Ry provided works great until I change part of it. In for formula, it reference C$2:C$99 but all data below C99 does not return the proper response. So I went in and changed it to C$2:C$45179 which is the bottom of my data in the column. It throws the results all off. Why does changing the range of part of the formula have such a dramatic effect? In the example there is a normal data set with the working formula and another data set with the changed formula that is no longer working.

Ok, I figured out what was happening.
The problem is in the people and the dates. The formula is counting the occurrences of the date to determine what is what. This works great when it is one person, but as soon as someone else comes up and the dates start repeating the formula can no longer tell which occurrence of the date should be labeled with what. We need to be able to distinguish between names.


Dean 12/20/2021 In
Dean 12/20/2021 Lunch
Dean 12/20/2021 Lunch
Dean 12/20/2021 Out
Sam 12/20/2021 Break
Sam 12/20/2021 Break
Sam 12/20/2021 Break

[nick.williams]

There is still some uncertainty to how you want to define Break and Lunch. Based on your description and example, breaks should be around 15 mins, and lunch should be around 30 mins. The below code (assuming it works correctly) categroises as Break if it's under 20 mins, but Lunch otherwise (with the first one of the day In and the last one Out):

=IF(ROW(C2)=MIN(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"In",IF(ROW(C2)=MAX(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2),ROW($C$2:$C$15),"")),"Out",IF(ABS(C2-INDEX(C:C,IF(MOD(SUM(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2)*(ROW($C$2:$C$15)<=ROW(C2)),1,0)),2)=0,MIN(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2)*(ROW($C$2:$C$15)>ROW(C2)),ROW($C$2:$C$15),"")),MAX(IF(($A$2:$A$15=A2)*($B$2:$B$15=B2)*(ROW($C$2:$C$15)<ROW(C2)),ROW($C$2:$C$15),"")))))< TIME(0,20,0),"Break","Lunch")))