Sick leave periods

[Iceman77]

Hello everyone
I want to find and have it in one cell the periods that a person used sick leave (for record purpose) in the month (see example below). I've tried to find something online, but haven't succeed.
By the way, still using excel 2010 .

For example:
A1 to AE1 are dates from Jan 1 to Jan 31
Cells D2, E2, F2, N2, O2, P2, Q2, W2, X2, Y2 and Z2 has letter S, which indicates the day person was on sick leave.
I need in cell AF2 to have the following
Jan 4-Jan 6, Jan 14-Jan17, Jan 23-Jan 26.

Thanks a lot.

 

[Sufiyan97]

Hi &
Welcome to Mr Excel!

If you are happy to have list in different rows rather than just AF2

Try

Book3
	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
1	1/1/2023	1/2/2023	1/3/2023	1/4/2023	1/5/2023	1/6/2023	1/7/2023	1/8/2023	1/9/2023	1/10/2023	1/11/2023	1/12/2023	1/13/2023	1/14/2023	1/15/2023	1/16/2023	1/17/2023	1/18/2023	1/19/2023	1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023	1/30/2023	1/31/2023
2				S	S	S								S	S	S	S						S	S	S	S						1/4/2023
3																																1/5/2023
4																																1/6/2023
5																																1/14/2023
6																																1/15/2023
7																																1/16/2023
8																																1/17/2023
9																																1/23/2023
10																																1/24/2023
11																																1/25/2023
12																																1/26/2023
13
14
Sheet1

Cell Formulas
Range	Formula
AF2:AF13	AF2	=IFERROR(INDEX($A$1:$AE$1,,AGGREGATE(15,6,COLUMN($A$2:$AE$2)-COLUMN($A$2)+1/($A$2:$AE$2="S"),ROWS($AF$2:AF2))),"")

 

[Iceman77]

Thanks for the quick response.
I kind did this in a different columns, but need it to be in one cell due to report reasons at the end of the month (among other things).

 

[Sufiyan97]

It can be done easily with Office 365, but I don't know how to deal with it in Excel 2010.

Hope someone else can help you.

 

[awoohaw]

TRY THIS:

=CONCAT(IF(A2:AE2="S",TEXT(A1:AE1,"yyyy-MM-DD")&", ",""))

 

[awoohaw]

TRY THIS:

=CONCAT(IF(A2:AE2="S",TEXT(A1:AE1,"yyyy-MM-DD")&", ",""))

This using array formulas you will need to enter the formula with the CNTL-SHIFT-ENTER method instead of pressing ENTER.

 

[Fluff]

2010 does not have Concat it came out with 2019.

 

[awoohaw]

what concatenate method was available in 2010? There had to have been one, Was it CONCATENATE?

 

[Fluff]

Was it CONCATENATE?

Yup, that was all, but it doesn't handle ranges or arrays.