Complex “consecutive weekday” formula

[bsafra1]

Hi, I have a report of employee IDs that contain information regarding those employees across a 98 day period. There is one row per employee per date, so one employee ID may have up to 98 rows of data but may have less.

Let’s say I have a second tab that removes duplicate Employee IDs and contains just a column of unique Employee IDs. If I wanted to add a column to the second tab that contains either a “Yes” or a “No” depending on whether there were any 10 consecutive weekdays of rows for the given Employee ID on the first tab, what is the best way to set that up?

 

[awoohaw]

Do you want to include days off / holidays as a weekday? in otherwords are you using a workweek (5 or 6 day) or an entire week? What about Holidays?

 

[bsafra1]

Monday through Friday

 

[awoohaw]

OKay, well, I was working on both ways. Use the 5 Day Week Formula. See if it works for you:

Book1
	A	B	C	D	E	F	G
1		5 Day Workweek	7 Day Workweek			5 Day Workweek	7 Day Workweek
2	Empl1	Fri 2023-09-01	Sat 2023-09-02		Empl1	No	Yes
3	Empl1	Mon 2023-09-04	Sun 2023-09-03		Empl2	Yes	No
4	Empl1	Tue 2023-09-05	Mon 2023-09-04		Empl3	Yes	No
5	Empl1	Wed 2023-09-06	Tue 2023-09-05
6	Empl1	Mon 2023-09-11	Tue 2023-09-12
7	Empl1	Tue 2023-09-12	Wed 2023-09-13
8	Empl1	Wed 2023-09-13	Thu 2023-09-14
9	Empl1	Fri 2023-09-15	Fri 2023-09-15
10	Empl1	Mon 2023-09-18	Sat 2023-09-16
11	Empl1	Wed 2023-09-20	Sun 2023-09-17
12	Empl1	Thu 2023-09-21	Mon 2023-09-18
13	Empl1	Fri 2023-09-22	Tue 2023-09-19
14	Empl1	Mon 2023-09-25	Wed 2023-09-20
15	Empl1	Tue 2023-09-26	Thu 2023-09-21
16	Empl1	Wed 2023-09-27	Fri 2023-09-22
17	Empl1	Thu 2023-09-28	Sat 2023-09-23
18	Empl1	Fri 2023-09-29	Sun 2023-09-24
19	Empl1	Mon 2023-10-02	Mon 2023-09-25
20	Empl1	Wed 2023-10-04	Tue 2023-09-26
21	Empl1	Thu 2023-10-05	Wed 2023-09-27
22	Empl2	Fri 2023-09-08	Thu 2023-09-28
23	Empl2	Mon 2023-09-11	Fri 2023-09-29
24	Empl2	Tue 2023-09-12	Wed 2023-10-04
25	Empl2	Wed 2023-09-13	Thu 2023-10-05
26	Empl2	Thu 2023-09-14	Fri 2023-10-06
27	Empl2	Fri 2023-09-15	Sat 2023-10-07
28	Empl2	Mon 2023-09-18	Sun 2023-10-08
29	Empl2	Tue 2023-09-19	Mon 2023-10-09
30	Empl2	Wed 2023-09-20	Tue 2023-10-10
31	Empl2	Thu 2023-09-21	Wed 2023-10-11
32	Empl2	Fri 2023-09-22	Thu 2023-10-12
33	Empl2	Mon 2023-09-25	Fri 2023-10-13
34	Empl2	Tue 2023-09-26	Sat 2023-10-14
35	Empl2	Wed 2023-09-27	Sun 2023-10-15
36	Empl2	Thu 2023-09-28	Mon 2023-10-16
37	Empl2	Fri 2023-09-29	Tue 2023-10-17
38	Empl2	Mon 2023-10-02	Wed 2023-10-18
39	Empl2	Tue 2023-10-03	Thu 2023-10-19
40	Empl2	Wed 2023-10-04	Fri 2023-10-20
41	Empl2	Thu 2023-10-05	Sat 2023-10-21
42	Empl2	Fri 2023-10-06	Sun 2023-10-22
43	Empl2	Mon 2023-10-09	Mon 2023-10-23
44	Empl2	Tue 2023-10-10	Tue 2023-10-24
45	Empl2	Wed 2023-10-11	Wed 2023-10-25
46	Empl2	Thu 2023-10-12	Thu 2023-10-26
47	Empl2	Fri 2023-10-13	Fri 2023-10-27
48	Empl2	Mon 2023-10-16	Sat 2023-10-28
49	Empl2	Tue 2023-10-17	Sun 2023-10-29
50	Empl2	Wed 2023-10-18	Mon 2023-10-30
51	Empl2	Thu 2023-10-19	Tue 2023-10-31
52	Empl2	Fri 2023-10-20	Wed 2023-11-01
53	Empl2	Mon 2023-10-23	Thu 2023-11-02
54	Empl2	Tue 2023-10-24	Fri 2023-11-03
55	Empl2	Wed 2023-10-25	Sat 2023-11-04
56	Empl3	Fri 2023-09-08	Sun 2023-11-05
57	Empl3	Mon 2023-09-11	Mon 2023-11-06
58	Empl3	Tue 2023-09-12	Tue 2023-11-07
59	Empl3	Wed 2023-09-13	Wed 2023-11-08
60	Empl3	Thu 2023-09-14	Sat 2023-11-11
61	Empl3	Fri 2023-09-15	Sun 2023-11-12
62	Empl3	Mon 2023-09-18	Mon 2023-11-13
63	Empl3	Tue 2023-09-19	Tue 2023-11-14
64	Empl3	Wed 2023-09-20	Wed 2023-11-15
65	Empl3	Thu 2023-09-21	Thu 2023-11-16
66	Empl3	Fri 2023-09-22	Fri 2023-11-17
67	Empl3	Mon 2023-09-25	Sat 2023-11-18
68	Empl3	Tue 2023-09-26	Sun 2023-11-19
69	Empl3	Wed 2023-09-27	Mon 2023-11-20
70	Empl3	Thu 2023-09-28	Fri 2023-11-24
71	Empl3	Fri 2023-09-29	Sat 2023-11-25
72	Empl3	Mon 2023-10-02	Sun 2023-11-26
73	Empl3	Tue 2023-10-03	Mon 2023-11-27
74	Empl3	Wed 2023-10-04	Wed 2023-11-29
75	Empl3	Thu 2023-10-05	Thu 2023-11-30
76	Empl3	Fri 2023-10-06	Fri 2023-12-01
77	Empl3	Sat 2023-10-07	Sat 2023-12-02
78	Empl3	Sun 2023-10-08	Sun 2023-12-03
79	Empl3	Mon 2023-10-09	Mon 2023-12-04
80	Empl3	Tue 2023-10-10	Tue 2023-12-05
81
bsafra

Cell Formulas
Range	Formula
F2:F4	F2	=IF(ISNUMBER(MATCH(0,WORKDAY.INTL(TAKE(CHOOSECOLS(FILTER($A$2:$B$80,$A$2:$A$80=E2,""),2),ROWS(FILTER($A$2:$B$80,$A$2:$A$80=E2,""))-10),10,1) - TAKE(CHOOSECOLS(FILTER($A$2:$B$80,$A$2:$A$80=E2,""),2),10-ROWS(FILTER($A$2:$B$80,$A$2:$A$80=E2,""))),0)), "Yes","No")
G2	G2	=IF(ISNUMBER(MATCH(0, TAKE(CHOOSECOLS(FILTER($A$2:$C$80,$A$2:$A$80=E2,""),3),10-ROWS(FILTER($A$2:$C$80,$A$2:$A$80=E2,"")))-10 - TAKE(CHOOSECOLS(FILTER($A$2:$C$80,$A$2:$A$80=E2,""),3),ROWS(FILTER($A$2:$C$80,$A$2:$A$80=E2,""))-10),0)), "Yes","No")
G3:G4	G3	=IF(ISNUMBER(MATCH(0, TAKE(CHOOSECOLS(FILTER($A$2:$C$80,$A$2:$A$80=G22,""),3),10-ROWS(FILTER($A$2:$C$80,$A$2:$A$80=G22,"")))-10 -TAKE(CHOOSECOLS(FILTER($A$2:$C$80,$A$2:$A$80=G22,""),3),ROWS(FILTER($A$2:$C$80,$A$2:$A$80=G22,""))-10),0)), "Yes","No")
B19	B19	=B18+3