List all holidays in a column to a single cell

mukulesh · Feb 19, 2018

I have a sheet where I have the calendar for the entire year. For a working day, the value on the adjacent cell is 0 (zero). Anything other than zero against a date will represent a holiday.

A sample week in my calendar is something like this:

Date	Holiday Remarks
22-Jan-2018	0
23-Jan-2018	0
24-Jan-2018	0
25-Jan-2018	0
26-Jan-2018	Republic Day
27-Jan-2018	0
28-Jan-2018	Sunday

Now, in another sheet, I want to list all holidays (only day of the date) between two dates. Input will be as follow:

Start Date:	01-Jan-2018
End Date:	31-Mar-2018

Finally I want the output as follows:

Months	Holidays
Jan	7, 14, 21,26,28
Feb	4,11,18,25
Mar	4,11,18,25

*I would like to avoid using VBA.

markmzz · Feb 20, 2018

Maybe the suggestion below (with only formulas) can help you.

Lets go:

1) Create two blank sheets: Main and Sheet1. Put your Holidays Data in the rage A2:B366 (for 2018) of the sheet Sheet1 and in A1 , A2, A4 and B4 of the sheet Main put the texts Start, End, Months and Holidays.

	A	B	C	D
1	Date	Holi		Sheet
2	seg-01/01/2018	New Year		Sheet1
3	ter-02/01/2018	0
4	qua-03/01/2018	0
5	qui-04/01/2018	0
6	sex-05/01/2018	0
7	sáb-06/01/2018	0
8	dom-07/01/2018	Sunday
9	seg-08/01/2018	0
10	ter-09/01/2018	0
11	qua-10/01/2018	0
12	qui-11/01/2018	0
13	sex-12/01/2018	0
14	sáb-13/01/2018	0
15	dom-14/01/2018	Sunday
16	seg-15/01/2018	0
***	****************	*********	****	*******

	A	B	C	D
1	Start	01/jan/18		Sheet
2	End	31/dez/18		Main
3
4	Months	Holidays
5	jan	1, 7, 14, 21, 28
6	fev	4, 11, 13, 18, 25
7	mar	4, 11, 18, 25, 30
8	abr	1, 8, 15, 21, 22, 29
9	mai	1, 6, 13, 20, 27, 31
10	jun	3, 10, 17, 24
11	jul	1, 8, 15, 22, 29
12	ago	5, 12, 19, 26
13	set	2, 7, 9, 16, 23, 30
14	out	7, 12, 14, 21, 28
15	nov	2, 4, 11, 15, 18, 25
16	dez	2, 9, 16, 23, 25, 30
***	********	****************	***	******

2) Now, create the names below:

Dates - Refers to: =Sheet1!$A$2:$A$366
Holis - Refers to: =Sheet1!$B$2:$B$366
Start - Refers to: =Main!$B$1
End - Refers to: =Main!$B$2
Months - Refers to: =Main!$A$5:$A$16 (for the 12 months of 2018)
SEMonths - Refers to: =(Dates>=Start)*(Dates<=End)*(Holis<>0)*MONTH(Dates)
DaysMonth - Refers to:

=IF(Dates>=Start,IF(Dates<=End,IF(Holis<>0,IF(MONTH(Dates)=MONTH(INDEX(Months,ROW()-ROW(Main!$A$5)+1)&"/"&YEAR(Start)),DAY(Dates)))))

3) Put the formula below in A5 in the sheet Main and copy down until the cell A16:

=IFERROR(TEXT(INDEX(MONTH(Dates),AGGREGATE(15,6,MATCH(ROW(INDIRECT("1:12")),
SEMonths,0),ROWS(A$5:A5)))&"/"&YEAR(Start),"mmm"),"")

4) Put the formula below in B5 in the sheet Main and copy down until the cell B16:

=IF(A5="","",SMALL(DaysMonth,1)&IFERROR(", "&SMALL(DaysMonth,2),"")&IFERROR(", "&SMALL(DaysMonth,3),"")&
IFERROR(", "&SMALL(DaysMonth,4),"")&IFERROR(", "&SMALL(DaysMonth,5),"")&IFERROR(", "&SMALL(DaysMonth,6),"")&
IFERROR(", "&SMALL(DaysMonth,7),"")&IFERROR(", "&SMALL(DaysMonth,8),"")&IFERROR(", "&SMALL(DaysMonth,9),""))

Ps1: if you think you need more days in each month, so edit the formula above.

Ps2: the holidays in the sheets are for my country.

I hope this helps.

Markmzz

markmzz · Feb 20, 2018

If you don't have Aggregate function, you can try this:

1) Create the name below:

SEMonths - Refers to: =IF(Dates>=Start,IF(Dates<=End,IF(Holis<>0,MONTH(Dates))))

2) Put the array formula (use Ctrl+Shift+Enter to the formula) below in B5 in the sheet Main and copy down until the cell B16:

=IFERROR(TEXT(INDEX(MONTH(Dates),SMALL(MATCH(ROW(INDIRECT("1:12")),
SEMonths,0),ROWS(A$5:A5)))&"/"&YEAR(Start),"mmm"),"")

Markmzz

wideboydixon · Feb 20, 2018

If you have Excel 2016 then you can use TEXTJOIN something like this:

Book1

A

B

1

Date

Holiday Remarks

2

01-Jan-2018

0

3

02-Jan-2018

0

4

03-Jan-2018

0

5

04-Jan-2018

0

6

05-Jan-2018

0

7

06-Jan-2018

0

8

07-Jan-2018

Sunday

9

08-Jan-2018

0

10

09-Jan-2018

0

11

10-Jan-2018

0

12

11-Jan-2018

0

13

12-Jan-2018

0

14

13-Jan-2018

0

15

14-Jan-2018

Sunday

16

15-Jan-2018

0

17

16-Jan-2018

0

18

17-Jan-2018

0

19

18-Jan-2018

0

20

19-Jan-2018

0

21

20-Jan-2018

0

22

21-Jan-2018

Sunday

23

22-Jan-2018

0

24

23-Jan-2018

0

25

24-Jan-2018

0

26

25-Jan-2018

0

27

26-Jan-2018

Republic Day

28

27-Jan-2018

0

29

28-Jan-2018

Sunday

Calendar

Book1

A

B

1

Start Date:

01-Jan-2018

2

End Date:

31-Mar-2018

3

4

Months

Holidays

5

6

7

Mar

4,11,18,25

8

9

10

11

12

13

14

15

16

Holiday List

Cell Formulas
Range	Formula
A5	=DATE(YEAR($B$1),MONTH($B$1),1)
A6	=IF($A5="","",IF(EOMONTH($A5,0)+1>$B$2,"",EOMONTH($A5,0)+1))
B5	{=TEXTJOIN(",",TRUE,IF(Calendar!$A$2:$A$367<$B$1,"",IF(Calendar!$A$2:$A$367>$B$2,"",IF(Calendar!$B$2:$B$367=0,"",IF($A5="","",IF(MONTH(Calendar!$A$2:$A$367)<>MONTH($A5),"",DAY(Calendar!$A$2:$A$367)))))))}
B6	{=TEXTJOIN(",",TRUE,IF(Calendar!$A$2:$A$367<$B$1,"",IF(Calendar!$A$2:$A$367>$B$2,"",IF(Calendar!$B$2:$B$367=0,"",IF($A6="","",IF(MONTH(Calendar!$A$2:$A$367)<>MONTH($A6),"",DAY(Calendar!$A$2:$A$367)))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in A6 down to A16. Copy formula in B6 down to B16.

WBD

markmzz · Feb 20, 2018

Another way (with the layout of the post #2):

1) Create the names below:

Dates - Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(8^7,Sheet1!$A:$A,1))
Holis - Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(8^7,Sheet1!$A:$A,1))
Start - Refers to: =Main!$B$1
End - Refers to: =Main!$B$2
Months - Refers to: =Main!$A$5:INDEX(Main!$A:$A,MATCH(8^7,Main!$A:$A,1))
DaysMonth - Refers to: =IF(DATE(YEAR(Dates),MONTH(Dates),1)=INDEX(Months,ROW()-ROW(INDEX(Months,1))+1),IF(Holis<>0,DAY(Dates)))

2) Put the formula below in A5 in the sheet Main and copy down:

=IF((DATEDIF(Start,End,"m")+1)>=ROWS(A$5:A5),EDATE(Start,ROWS(A$5:A5)-1),"")

4) Format the cell B5 with the custom format mmm/yy and put the formula below in B5 in the sheet Main and copy down:

=IF(A5="","",SMALL(DaysMonth,1)&IFERROR(", "&SMALL(DaysMonth,2),"")&IFERROR(", "&SMALL(DaysMonth,3),"")&
IFERROR(", "&SMALL(DaysMonth,4),"")&IFERROR(", "&SMALL(DaysMonth,5),"")&IFERROR(", "&SMALL(DaysMonth,6),"")&
IFERROR(", "&SMALL(DaysMonth,7),"")&IFERROR(", "&SMALL(DaysMonth,8),"")&IFERROR(", "&SMALL(DaysMonth,9),""))

	A	B	C	D
1	Start	01/jan/18		Sheet
2	End	31/mar/19		Main
3
4	Months	Holidays
5	jan/2018	1, 7, 14, 21, 28
6	fev/2018	4, 11, 13, 18, 25
7	mar/2018	4, 11, 18, 25, 30
8	abr/2018	1, 8, 15, 21, 22, 29
9	mai/2018	1, 6, 13, 20, 27, 31
10	jun/2018	3, 10, 17, 24
11	jul/2018	1, 8, 15, 22, 29
12	ago/2018	5, 12, 19, 26
13	set/2018	2, 7, 9, 16, 23, 30
14	out/2018	7, 12, 14, 21, 28
15	nov/2018	2, 4, 11, 15, 18, 25
16	dez/2018	2, 9, 16, 23, 25, 30
17	jan/2019	1, 6, 13, 20, 27
18	fev/2019	3, 10, 17, 24
19	mar/2019	3, 5, 10, 17, 24, 31
***	********	****************	***	******

Markmzz

mukulesh · Mar 2, 2018

Dear Markmzz,
Thanks a lot for the solution. I don't need the holidays of the whole year at a time. The Start Date and the End dates are variable.

e.g. Start Date: 08-Jan-2018
End Date: 25-Feb-2018

OR
Start Date: 08-Jan-2018
End Date: 20-Mar-2018

I want only those holidays which fall between Start Date and the End Date, on month-wise basis. I will keep 3 rows x 2 columns reserved for the output assuming that not more than 3 months fall between Start Date and End Date.

mukulesh · Mar 2, 2018

Dear wideboydixon
Thanks a lot for the solution you provided. But, sorry for I am not using Excel 2016. Can you please give another solution?

markmzz · Mar 2, 2018

mukulesh said:
Dear Markmzz,
Thanks a lot for the solution. I don't need the holidays of the whole year at a time. The Start Date and the End dates are variable.

e.g. Start Date: 08-Jan-2018
End Date: 25-Feb-2018

OR
Start Date: 08-Jan-2018
End Date: 20-Mar-2018

I want only those holidays which fall between Start Date and the End Date, on month-wise basis. I will keep 3 rows x 2 columns reserved for the output assuming that not more than 3 months fall between Start Date and End Date.

Did you try my suggestion of post #2?

Look at this:

	A	B	C	D
1	Start	08/jan/18		Sheet
2	End	25/fev/18		Main
3
4	Months	Holidays
5	jan	14, 21, 28
6	fev	4, 11, 13, 18, 25
7
***	********	****************	***	******

And this:

	A	B	C	D
1	Start	08/jan/18		Sheet
2	End	20/mar/18		Main
3
4	Months	Holidays
5	jan	14, 21, 28
6	fev	4, 11, 13, 18, 25
7	mar	4, 11, 18
***	********	****************	***	******

Markmzz

markmzz · Mar 2, 2018

mukulesh said:
Dear Markmzz,
Thanks a lot for the solution. I don't need the holidays of the whole year at a time. The Start Date and the End dates are variable.

e.g. Start Date: 08-Jan-2018
End Date: 25-Feb-2018

OR
Start Date: 08-Jan-2018
End Date: 20-Mar-2018

I want only those holidays which fall between Start Date and the End Date, on month-wise basis. I will keep 3 rows x 2 columns reserved for the output assuming that not more than 3 months fall between Start Date and End Date.

Here is a small modification in my suggestion of the post #2:

Lets go:

1) Create two blank sheets: Main and Sheet1. Put your Holidays Data in the rage A2:B366 (for 2018 - or more if you want) of the sheet Sheet1 and in A1, A2, A4 and B4 of the sheet Main put the texts Start:, End:, Months and Holidays.

2) Now, create the names below:

Dates - Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(8^7,Sheet1!$A:$A,1))
Holis - Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(8^7,Sheet1!$A:$A,1))
Start - Refers to: =Main!$B$1
End - Refers to: =Main!$B$2
Months - Refers to: =Main!$A$5:$A$7 (like you said: only 03 months)
SEMonths
- Refers to: =IFERROR(MATCH(ROW(INDIRECT("1:12")),(Dates>=Start)*(Dates<=End)*(Holis<>0)*MONTH(Dates),0),FALSE)

DaysMonth

- Refers to: =IF(Dates>=Start,IF(Dates<=End,IF(Holis<>0,IF(MONTH(Dates)=MONTH(INDEX(Months,ROW()-ROW(INDEX(Months,1))+1)&"/"&YEAR(Start)),DAY(Dates)))))

3) Put the formula below in A5 in the sheet Main and copy down until A7 (like you said: only 03 months):

=IFERROR(TEXT(INDEX(MONTH(Dates),SMALL(SEMonths,ROWS(A$5:A5)))&"/"&YEAR(Start),"mmm"),"")

4) Put the formula below in B5 in the sheet Main and copy down until B7 (like you said: only 03 months):

=IF(A5="","",SMALL(DaysMonth,1)&IFERROR(", "&SMALL(DaysMonth,2),"")&IFERROR(", "&SMALL(DaysMonth,3),"")&
IFERROR(", "&SMALL(DaysMonth,4),"")&IFERROR(", "&SMALL(DaysMonth,5),"")&IFERROR(", "&SMALL(DaysMonth,6),"")&
IFERROR(", "&SMALL(DaysMonth,7),"")&IFERROR(", "&SMALL(DaysMonth,8),"")&IFERROR(", "&SMALL(DaysMonth,9),""))

Ps1: if you think you need more days in each month, so edit the formula above.
Ps2: the holidays in the sheets are for my country.

I hope this helps.

Markmzz

mukulesh · Mar 2, 2018

markmzz said:
Here is a small modification in my suggestion of the post #2:

Lets go:

1) Create two blank sheets: Main and Sheet1. Put your Holidays Data in the rage A2:B366 (for 2018 - or more if you want) of the sheet Sheet1 and in A1, A2, A4 and B4 of the sheet Main put the texts Start:, End:, Months and Holidays.

2) Now, create the names below:

Dates - Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(8^7,Sheet1!$A:$A,1))
Holis - Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(8^7,Sheet1!$A:$A,1))
Start - Refers to: =Main!$B$1
End - Refers to: =Main!$B$2
Months - Refers to: =Main!$A$5:$A$7 (like you said: only 03 months)
SEMonths
- Refers to: =IFERROR(MATCH(ROW(INDIRECT("1:12")),(Dates>=Start)*(Dates<=End)*(Holis<>0)*MONTH(Dates),0),FALSE)

DaysMonth
- Refers to: =IF(Dates>=Start,IF(Dates<=End,IF(Holis<>0,IF(MONTH(Dates)=MONTH(INDEX(Months,ROW()-ROW(INDEX(Months,1))+1)&"/"&YEAR(Start)),DAY(Dates)))))

3) Put the formula below in A5 in the sheet Main and copy down until A7 (like you said: only 03 months):

=IFERROR(TEXT(INDEX(MONTH(Dates),SMALL(SEMonths,ROWS(A$5:A5)))&"/"&YEAR(Start),"mmm"),"")

4) Put the formula below in B5 in the sheet Main and copy down until B7 (like you said: only 03 months):

=IF(A5="","",SMALL(DaysMonth,1)&IFERROR(", "&SMALL(DaysMonth,2),"")&IFERROR(", "&SMALL(DaysMonth,3),"")&
IFERROR(", "&SMALL(DaysMonth,4),"")&IFERROR(", "&SMALL(DaysMonth,5),"")&IFERROR(", "&SMALL(DaysMonth,6),"")&
IFERROR(", "&SMALL(DaysMonth,7),"")&IFERROR(", "&SMALL(DaysMonth,8),"")&IFERROR(", "&SMALL(DaysMonth,9),""))

Ps1: if you think you need more days in each month, so edit the formula above.
Ps2: the holidays in the sheets are for my country.

I hope this helps.

Markmzz

The formula on A5 is returning blank (#NUM error when IFERROR is removed).

List all holidays in a column to a single cell

mukulesh

New Member

Excel Facts

markmzz

MrExcel MVP

markmzz

MrExcel MVP

wideboydixon

Well-known Member

markmzz

MrExcel MVP

mukulesh

New Member

mukulesh

New Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

mukulesh

New Member

Similar threads

Forum statistics

Share this page

List all holidays in a column to a single cell

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock