Grabbing any two characters instead of defined.

[SSr00]

I am having trouble getting the below duty roster to pick up any two characters, instead of only "DD". The goal is to count how many days since a person has last done a weekday or weekend duty from the end of the month, the input can be any two characters. Bonus points for anyone who can also figure out a way to get it to run without using the LET function, as this does not work with excel at work.

Any help at all would be hugely appreciated.

=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"),maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))

Duties.DoNotEdit.1 - Maseter.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	01 Sep 23					01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	01
3						Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Pte			1	0																				DD												7	5
5	Pte			0	1																							DD									21	1
6	Pte			0	1																														DD		21	0
7	Pte			1	0																													DD			0	5
8	Pte			0	0																																21	5
9	Pte			0	0																																21	5
10	Pte			0	0																																21	5
11	Pte			0	0																																21	5
12	Pte			0	0																																21	5
13	Pte			0	0																																21	5
14	Pte			0	0																																21	5
15	Pte			0	0																																21	5
Template

Cell Formulas
Range	Formula
F1	F1	=A1
G1:AJ1	G1	=F1+1
D4:D15	D4	=SUMPRODUCT((WEEKDAY($F$2:$AI$2,2)<6)*(F4:AI4 = "DD"))
E4:E15	E4	=SUMPRODUCT((WEEKDAY($F$2:$AI$2,2)>5)*(F4:AI4 = "DD"))
AK4	AK4	=LET(days,FILTER(F4:AJ4,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL4	AL4	=ROUNDDOWN((LET( days, FILTER(F4:AJ4, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK5	AK5	=LET(days,FILTER(F5:AJ5,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL5	AL5	=ROUNDDOWN((LET( days, FILTER(F5:AJ5, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK6	AK6	=LET(days,FILTER(F6:AJ6,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL6	AL6	=ROUNDDOWN((LET( days, FILTER(F6:AJ6, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK7	AK7	=LET(days,FILTER(F7:AJ7,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL7	AL7	=ROUNDDOWN((LET( days, FILTER(F7:AJ7, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK8	AK8	=LET(days,FILTER(F8:AJ8,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL8	AL8	=ROUNDDOWN((LET( days, FILTER(F8:AJ8, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK9	AK9	=LET(days,FILTER(F9:AJ9,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL9	AL9	=ROUNDDOWN((LET( days, FILTER(F9:AJ9, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK10	AK10	=LET(days,FILTER(F10:AJ10,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL10	AL10	=ROUNDDOWN((LET( days, FILTER(F10:AJ10, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK11	AK11	=LET(days,FILTER(F11:AJ11,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL11	AL11	=ROUNDDOWN((LET( days, FILTER(F11:AJ11, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK12	AK12	=LET(days,FILTER(F12:AJ12,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL12	AL12	=ROUNDDOWN((LET( days, FILTER(F12:AJ12, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK13	AK13	=LET(days,FILTER(F13:AJ13,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL13	AL13	=ROUNDDOWN((LET( days, FILTER(F13:AJ13, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK14	AK14	=LET(days,FILTER(F14:AJ14,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL14	AL14	=ROUNDDOWN((LET( days, FILTER(F14:AJ14, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)
AK15	AK15	=LET(days,FILTER(F15:AJ15,LEFT(F3:AJ3)<>"S"), maxCount,COLUMNS(days),maxCount-IFERROR(XMATCH("DD",days,0,-1),0))
AL15	AL15	=ROUNDDOWN((LET( days, FILTER(F15:AJ15, LEFT(F3:AJ3)="S"), maxCount, COLUMNS(days), maxCount - IFERROR(XMATCH("DD", days, 0, -1), 0) ))/2, 0)

Named Ranges
Name	Refers To	Cells
DutyRange	=Template!$F$2:$AG$20	D4:E15
MyRange	=Template!$F$4:$J$4,Template!$M$4:$Q$4,Template!$T$4:$X$4,Template!$AA$4:$AE$4	AK4:AL4, D4:E4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E50	Cell Value	>1	text	NO
D4:D50	Cell Value	>2	text	NO

EDIT:
Also, the headers in AK and AL should read "Last Weekday" + "Last Weekend" respectively.

 

[SSr00]

My pleasure

I have been since working with the above formula whilst trying to modify it, but I still can't seem to fix one issue. The count will always be short one weekend, so for example Jan 2024, it will count 3 weekends instead of 4. So, when someone is rostered for the first weekend of a month, it won't show that because everyone who has not been rostered for that month shows as "3"

Hope that makes sense. Thanks again!

 

[Fluff]

Which formula are you talking about?
Also can you post some data that shows the problem along with the expected result.

 

[SSr00]

Duties.DoNotEdit.1 - Master.copy.xlsx
	A	B	C	D	E		G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	01 Jan 24						01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
2		WD	WE	No.WD	No.WE		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Last WD	Last WE
4	Pte			0	0																																	22	03
5	Pte			0	1							DD																										22	03
6	Pte			0	1														STD																			22	02
7	Pte			0	1																					OO												22	01
8	Pte			0	1																												DD					22	00
9	Pte			0	0																																	22	03
10	Pte			0	0																																	22	03
11	Pte			0	0																																	22	03
12	Pte			0	0																																	22	03
13	Pte			0	0																																	22	03
Main Gate

Cell Formulas
Range	Formula
G1	G1	=A1
H1:AK2	H1	=G1+1
G2	G2	=A1
D4:D13	D4	=SUMPRODUCT((WEEKDAY($G$2:$AJ$2,2)<6)*(G4:AJ4<>""))
E4:E13	E4	=SUMPRODUCT((WEEKDAY($G$2:$AJ$2,2)>5)*(G4:AJ4 <>""))
AL4:AL13	AL4	=NETWORKDAYS(IFNA(LOOKUP(2,1/((LEFT($G$3:$AK$3)<>"s")*(G4:AK4<>"")),$G$1:$AK$1),$G$1),$AK$1)-1
AM4:AM13	AM4	=FLOOR((NETWORKDAYS.INTL(IFNA(LOOKUP(2,1/((LEFT($G$3:$AK$3)="s")*(G4:AK4<>"")),$G$1:$AK$1),$G$1),$AK$1,"1111100")-1)/2,1)

Named Ranges
Name	Refers To	Cells
DutyRange	='Main Gate'!$G$2:$AH$20	D4:E13, H2
MyRange	='Main Gate'!$G$4:$K$4,'Main Gate'!$N$4:$R$4,'Main Gate'!$U$4:$Y$4,'Main Gate'!$AB$4:$AF$4	AL4:AM4, D4:E4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AK1	Expression	=COUNTA($AK$4:$AK$50)>2	text	NO
AK1	Expression	=AND(COUNTIF(AK4:AK50, "EG")>=1, COUNTIF(AK4:AK50, "LG")>=1)	text	NO
AJ1	Expression	=COUNTA($AJ$4:$AJ$50)>2	text	NO
AJ1	Expression	=AND(COUNTIF(AJ4:AJ50, "EG")>=1, COUNTIF(AJ4:AJ50, "LG")>=1)	text	NO
AI1	Expression	=COUNTA($AI$4:$AI$50)>2	text	NO
AI1	Expression	=AND(COUNTIF(AI4:AI50, "EG")>=1, COUNTIF(AI4:AI50, "LG")>=1)	text	NO
AH1	Expression	=COUNTA($AH$4:$AH$50)>2	text	NO
AH1	Expression	=AND(COUNTIF(AH4:AH50, "EG")>=1, COUNTIF(AH4:AH50, "LG")>=1)	text	NO
AG1	Expression	=COUNTA($AG$4:$AG$50)>2	text	NO
AG1	Expression	=AND(COUNTIF(AG4:AG50, "EG")>=1, COUNTIF(AG4:AG50, "LG")>=1)	text	NO
AF1	Expression	=COUNTA($AF$4:$AF$50)>2	text	NO
AF1	Expression	=AND(COUNTIF(AF4:AF50, "EG")>=1, COUNTIF(AF4:AF50, "LG")>=1)	text	NO
AE1	Expression	=COUNTA($AE$4:$AE$50)>2	text	NO
AE1	Expression	=AND(COUNTIF(AE4:AE50, "EG")>=1, COUNTIF(AE4:AE50, "LG")>=1)	text	NO
AD1	Expression	=COUNTA($AD$4:$AD$50)>2	text	NO
AD1	Expression	=AND(COUNTIF(AD4:AD50, "EG")>=1, COUNTIF(AD4:AD50, "LG")>=1)	text	NO
AC1	Expression	=COUNTA($AC$4:$AC$50)>2	text	NO
AC1	Expression	=AND(COUNTIF(AC4:AC50, "EG")>=1, COUNTIF(AC4:AC50, "LG")>=1)	text	NO
AB1	Expression	=COUNTA($AB$4:$AB$50)>2	text	NO
AB1	Expression	=AND(COUNTIF(AB4:AB50, "EG")>=1, COUNTIF(AB4:AB50, "LG")>=1)	text	NO
AA1	Expression	=COUNTA($AA$4:$AA$50)>2	text	NO
AA1	Expression	=AND(COUNTIF(AA4:AA50, "EG")>=1, COUNTIF(AA4:AA50, "LG")>=1)	text	NO
Z1	Expression	=COUNTA($Z$4:$Z$50)>2	text	NO
Z1	Expression	=AND(COUNTIF(Z4:Z50, "EG")>=1, COUNTIF(Z4:Z50, "LG")>=1)	text	NO
Y1	Expression	=COUNTA($Y$4:$Y$50)>2	text	NO
Y1	Expression	=AND(COUNTIF(Y4:Y50, "EG")>=1, COUNTIF(Y4:Y50, "LG")>=1)	text	NO
X1	Expression	=COUNTA($X$4:$X$50)>2	text	NO
X1	Expression	=AND(COUNTIF(X4:X50, "EG")>=1, COUNTIF(X4:X50, "LG")>=1)	text	NO
W1	Expression	=COUNTA($W$4:$W$50)>2	text	NO
W1	Expression	=AND(COUNTIF(W4:W50, "EG")>=1, COUNTIF(W4:W50, "LG")>=1)	text	NO
V1	Expression	=COUNTA($V$4:$V$50)>2	text	NO
V1	Expression	=AND(COUNTIF(V4:V50, "EG")>=1, COUNTIF(V4:V50, "LG")>=1)	text	NO
U1	Expression	=COUNTA($U$4:$U$50)>2	text	NO
U1	Expression	=AND(COUNTIF(U4:U50, "EG")>=1, COUNTIF(U4:U50, "LG")>=1)	text	NO
T1	Expression	=COUNTA($T$4:$T$50)>2	text	NO
T1	Expression	=AND(COUNTIF(T4:T50, "EG")>=1, COUNTIF(T4:T50, "LG")>=1)	text	NO
S1	Expression	=COUNTA($S$4:$S$50)>2	text	NO
S1	Expression	=AND(COUNTIF(S4:S50, "EG")>=1, COUNTIF(S4:S50, "LG")>=1)	text	NO
R1	Expression	=COUNTA($R$4:$R$50)>2	text	NO
R1	Expression	=AND(COUNTIF(R4:R50, "EG")>=1, COUNTIF(R4:R50, "LG")>=1)	text	NO
Q1	Expression	=COUNTA($Q$4:$Q$50)>2	text	NO
Q1	Expression	=AND(COUNTIF(Q4:Q50, "EG")>=1, COUNTIF(Q4:Q50, "LG")>=1)	text	NO
P1	Expression	=COUNTA($P$4:$P$50)>2	text	NO
P1	Expression	=AND(COUNTIF(P4:P50, "EG")>=1, COUNTIF(P4:P50, "LG")>=1)	text	NO
N1	Expression	=COUNTA($N$4:$N$50)>2	text	NO
O1	Expression	=COUNTA($O$4:$O$50)>2	text	NO
N1:O1	Expression	=AND(COUNTIF(N4:N50, "EG")>=1, COUNTIF(N4:N50, "LG")>=1)	text	NO
M1	Expression	=COUNTA($M$4:$M$50)>2	text	NO
M1	Expression	=AND(COUNTIF(M4:M50, "EG")>=1, COUNTIF(M4:M50, "LG")>=1)	text	NO
L1	Expression	=COUNTA($L$4:$L$50)>2	text	NO
L1	Expression	=AND(COUNTIF(L4:L50, "EG")>=1, COUNTIF(L4:L50, "LG")>=1)	text	NO
K1	Expression	=COUNTA($K$4:$K$50)>2	text	NO
K1	Expression	=AND(COUNTIF(K4:K50, "EG")>=1, COUNTIF(K4:K50, "LG")>=1)	text	NO
I1	Expression	=COUNTA($I$4:$I$50)>2	text	NO
I1	Expression	=AND(COUNTIF(I4:I50, "EG")>=1, COUNTIF(I4:I50, "LG")>=1)	text	NO
H1	Expression	=COUNTA($H$4:$H$50)>2	text	NO
H1	Expression	=AND(COUNTIF(H4:H50, "EG")>=1, COUNTIF(H4:H50, "LG")>=1)	text	NO
G1	Expression	=COUNTA($G$4:$G$50)>2	text	NO
G1	Expression	=AND(COUNTIF(G4:G50, "EG")>=1, COUNTIF(G4:G50, "LG")>=1)	text	NO
D4:F50	Cell Value	>2	text	NO

My apologies. So as can be seen in the end column "Last WE", which counts the amount of weekends since someone was rostered. It should be at 04 and not 03 as there are 4 weekends in January.

 

[SSr00]

=FLOOR((NETWORKDAYS.INTL(IFNA(LOOKUP(2,1/((LEFT($G$3:$AK$3)="s")*(G4:AK4<>"")),$G$1:$AK$1),$G$1),$AK$1,"1111100")-1)/2,1)

That is the formula in question.

 

[Fluff]

Do you have the Let function now?

 

[SSr00]

Do you have the Let function now?

Unfortunately not at work, no.

 

[Fluff]

Ok, how about

=FLOOR(IFNA(NETWORKDAYS.INTL(LOOKUP(2,1/((LEFT($G$3:$AK$3)="s")*(G4:AK4<>"")),$G$1:$AK$1),$AK$1,"1111100")-1,NETWORKDAYS.INTL($G$1,$AK$1,"1111100"))/2,1)

 

[SSr00]

Ok, how about

=FLOOR(IFNA(NETWORKDAYS.INTL(LOOKUP(2,1/((LEFT($G$3:$AK$3)="s")*(G4:AK4<>"")),$G$1:$AK$1),$AK$1,"1111100")-1,NETWORKDAYS.INTL($G$1,$AK$1,"1111100"))/2,1)

I wish you could see how happy this makes me lol. Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback