Search the Day Off Dates in a Given ROW

[Armstrong_N14]

Good Day Team

Any suggestions on how can I Find the dates that show Days Off in a given row? I can't use XLBB due to restrictions.

A1				Sun	Mon	Tue	Wed	Thu	Fri	Sat
A2	Agent Name	Shift Start	Shift End	09/1/2023	09/2/2023	09/3/2023	09/04/2023	09/05/2023	09/06/2023	09/07/2023	Days OFF
A3	Employee 1	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF
A4	Employee 2	00:00	09:00	OFF	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF
A5	Employee 3	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF
A6	Employee 4	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF

I tried using the match formula but it only returns the first date that is on OFF. I was trying to see if searching from right to left may do it but I cant get the right formula. Please help. Thanks in advance

 

[noveske]

Like this?

K3

=IF(D3="OFF", D1, "") & IF(E3="OFF", " " & E1, "") & IF(F3="OFF", " " & F1, "") & IF(G3="OFF", " " & G1, "") & IF(H3="OFF", " " & H1, "") & IF(I3="OFF", " " & I1, "") & IF(J3="OFF", " " & J1, "")

K4

=IF(D4="OFF", D1, "") & IF(E4="OFF", " " & E1, "") & IF(F4="OFF", " " & F1, "") & IF(G4="OFF", " " & G1, "") & IF(H4="OFF", " " & H1, "") & IF(I4="OFF", " " & I1, "") & IF(J4="OFF", " " & J1, "")

K5

=IF(D5="OFF", D1, "") & IF(E5="OFF", " " & E1, "") & IF(F5="OFF", " " & F1, "") & IF(G5="OFF", " " & G1, "") & IF(H5="OFF", " " & H1, "") & IF(I5="OFF", " " & I1, "") & IF(J5="OFF", " " & J1, "")

K6

=IF(D6="OFF", D1, "") & IF(E6="OFF", " " & E1, "") & IF(F6="OFF", " " & F1, "") & IF(G6="OFF", " " & G1, "") & IF(H6="OFF", " " & H1, "") & IF(I6="OFF", " " & I1, "") & IF(J6="OFF", " " & J1, "")

 

[Armstrong_N14]

Thanks, This is a good solution and is appreciated a lot.

 

[awoohaw]

Try this:
this had errors. trying to update. Sorry.

You have me confused 9/1/2023 is not a SUNDAY!

 

[Armstrong_N14]

naiu

Try this:
this had errors. trying to update. Sorry.

You have me confused 9/1/2023 is not a SUNDAY!

Nailed it. Awesome.
Thanks a ton!

 

[awoohaw]

You're welcome, best wishes.
Here is the corrected version, with day of week in the header column to show you why I was confused:

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1				Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	Agent Name	Shift Start	Shift End	Fri 2023-09-01	Sat 2023-09-02	Sun 2023-09-03	Mon 2023-09-04	Tue 2023-09-05	Wed 2023-09-06	Thu 2023-09-07	Days OFF
3	Employee 1	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF	Wed, Thu
4	Employee 2	00:00	09:00	OFF	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	00:00 - 09:00, OFF
5	Employee 3	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF	00:00 - 09:00, OFF
6	Employee 4	00:00	09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	00:00 - 09:00	OFF	OFF	OFF, OFF
Armstrong_N14

Cell Formulas
Range	Formula
E2:J2	E2	=D2+1
K3:K6	K3	=TEXTJOIN(", ",,TEXT(FILTER(D2:J2,(D3:J3="OFF")),"ddd"))

 

[Armstrong_N14]

naiu

Nailed it. Awesome.
Thanks a ton!

Sorry for that. Silly me.