List all holidays in a column to a single cell

M

mukulesh

New Member
Joined
Feb 19, 2018
Messages
11
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:

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Holiday Remarks[/TD]
[/TR]
[TR]
[TD="align: center"]22-Jan-2018[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]23-Jan-2018[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]24-Jan-2018[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]25-Jan-2018[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]26-Jan-2018[/TD]
[TD="align: center"]Republic Day[/TD]
[/TR]
[TR]
[TD="align: center"]27-Jan-2018[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]28-Jan-2018[/TD]
[TD="align: center"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]

​

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

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: right"]Start Date:[/TD]
[TD="align: center"]01-Jan-2018[/TD]
[/TR]
[TR]
[TD="align: right"]End Date:[/TD]
[TD="align: center"]31-Mar-2018[/TD]
[/TR]
</tbody>[/TABLE]

Finally I want the output as follows:

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: center"]Months[/TD]
[TD="align: center"]Holidays[/TD]
[/TR]
[TR]
[TD="align: center"]Jan[/TD]
[TD="align: center"]7, 14, 21,26,28[/TD]
[/TR]
[TR]
[TD="align: center"]Feb[/TD]
[TD="align: center"]4,11,18,25[/TD]
[/TR]
[TR]
[TD="align: center"]Mar[/TD]
[TD="align: center"]4,11,18,25[/TD]
[/TR]
</tbody>[/TABLE]

*I would like to avoid using VBA.
 
Sort by date Sort by votes
mukulesh said:
The formula on A5 is returning blank (#NUM error when IFERROR is removed).
Click to expand...

Here all is Ok. Look at this:

[TABLE="class: grid, width: 289"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD="align: right"]08/jan/18[/TD]
[TD][/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]End[/TD]
[TD="align: right"]20/mar/18[/TD]
[TD][/TD]
[TD]Main[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Months[/TD]
[TD]Holidays[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]jan[/TD]
[TD]14, 21, 28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]fev[/TD]
[TD]4, 11, 13, 18, 25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]mar[/TD]
[TD]4, 11, 18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]********[/TD]
[TD]****************[/TD]
[TD]***[/TD]
[TD]******[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 331"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Holi[/TD]
[TD][/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]seg-01/01/2018[/TD]
[TD]New Year[/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ter-02/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]qua-03/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]qui-04/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]sex-05/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]sáb-06/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]dom-07/01/2018[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]seg-08/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]359[/TD]
[TD]seg-24/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]360[/TD]
[TD]ter-25/12/2018[/TD]
[TD]Natal[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]361[/TD]
[TD]qua-26/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]362[/TD]
[TD]qui-27/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]363[/TD]
[TD]sex-28/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]364[/TD]
[TD]sáb-29/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]365[/TD]
[TD]dom-30/12/2018[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]366[/TD]
[TD]seg-31/12/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*****************[/TD]
[TD]*********[/TD]
[TD]****[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]

Could you post your formulas and the sheets layout (like my sheets layout above)?

Markmzz
 
Last edited:
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Dear @Markmzz,
Extremely sorry for replying (too) late as I got stuck with my work. Off late I could succeed with your solution on Post #2 . What problem now I am facing is that with naming the ranges. I'll have multiple Start Date and End Date (so with Months, DaysMonth and SEMonths) as I'm preparing a tour plan for a quarter or two of the year, where there are many places visited, each with their own Start Date and End Date. Could you please provide some solution without using naming the ranges? Thanking you in advance.
 
Upvote 0
mukulesh said:
Dear @Markmzz,
Extremely sorry for replying (too) late as I got stuck with my work. Off late I could succeed with your solution on Post #2 . What problem now I am facing is that with naming the ranges. I'll have multiple Start Date and End Date (so with Months, DaysMonth and SEMonths) as I'm preparing a tour plan for a quarter or two of the year, where there are many places visited, each with their own Start Date and End Date. Could you please provide some solution without using naming the ranges? Thanking you in advance.
Click to expand...

Hi!

Could you post a workbook with a example of what you have and what you want in OneDrive?

Markmzz
​
 
Upvote 0
I get notified when this thread updates so I thought I'd have another go. If you're prepared to use some helper columns on one sheet, you can do this without TEXTJOIN like this:


Book1
AB
1DateHoliday Remarks
201-Jan-20180
302-Jan-20180
403-Jan-20180
504-Jan-20180
605-Jan-20180
706-Jan-20180
807-Jan-2018Sunday
908-Jan-20180
1009-Jan-20180
1110-Jan-20180
1211-Jan-20180
1312-Jan-20180
1413-Jan-20180
1514-Jan-2018Sunday
1615-Jan-20180
1716-Jan-20180
1817-Jan-20180
1918-Jan-20180
2019-Jan-20180
2120-Jan-20180
2221-Jan-2018Sunday
2322-Jan-20180
2423-Jan-20180
2524-Jan-20180
2625-Jan-20180
2726-Jan-2018Republic Day
2827-Jan-20180
2928-Jan-2018Sunday
3029-Jan-20180
3130-Jan-20180
3231-Jan-20180
Calendar



Book1
ABCDEFGHIJK
1Start Date:01-Jan-2018
2End Date:31-Dec-2018
3
4MonthsHolidays
5Jan7,14,21,26,28,7,14,21,26,28
6Feb4,11,18,25,4,11,18,25
7Mar4,11,18,25,4,11,18,25
8Apr1,8,15,22,29,1,8,15,22,29
9May6,13,20,27,6,13,20,27
10Jun3,10,17,24,3,10,17,24
11Jul1,8,15,22,29,1,8,15,22,29
12Aug5,12,19,26,5,12,19,26
13Sep2,9,16,23,30,2,9,16,23,30
14Oct7,14,21,28,7,14,21,28
15Nov4,11,18,25,4,11,18,25
16Dec2,9,16,23,30,2,9,16,23,30
Holiday List
Cell Formulas
RangeFormula
A5=DATE(YEAR($B$1),MONTH($B$1),1)
A6=IF($A5="","",IF(EOMONTH($A5,0)+1>$B$2,"",EOMONTH($A5,0)+1))
B5=MID($C5&$D5&$E5&$F5&$G5&$H5&$I5&$J5&$K5,2,1024)
C5{=IFERROR(","&SMALL(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)))))),COLUMNS($C5:C5)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


Copy formula in A6 down to A16. Copy formula in B5 down to B16. Copy formula in C5 to the whole range C5:K16.

WBD
 
Upvote 0
markmzz said:
Hi!

Could you post a workbook with a example of what you have and what you want in OneDrive?

Markmzz
​
Click to expand...

Until that, try this (only with the names Dates and Holis and layout of the post #11):

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

Code: 
[COLOR=#0000ff]=TEXT(EOMONTH($A$1,ROW()-ROW($A$5)),"mmm")

[/COLOR]Or you can use this:[COLOR=#0000ff]

[/COLOR][COLOR=#0000ff]=IF(ROW()-ROW($B$5)>MONTH($B$2)-MONTH($B$1),"",TEXT(EOMONTH($B$1,ROW()-ROW($B$5)),"mmm"))[/COLOR]

2) Put the big array formula (use Ctrl+Shift+Enter to enter the formula) below in B5 in the sheet Main and copy down until B7 (like you said: only 03 months):

Code: 
[COLOR=#0000ff]=IF(A5="","",
IFERROR(SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),1),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),2),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),3),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),4),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),5),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),6),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),7),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),8),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),9),""))[/COLOR]

Markmzz
 
Upvote 0
Numbers of IFs (in that big array formula) are not allowed even in .xlsx format!!

markmzz said:
Until that, try this (only with the names Dates and Holis and layout of the post #11):

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

Code: 
[COLOR=#0000ff]=TEXT(EOMONTH($A$1,ROW()-ROW($A$5)),"mmm")

[/COLOR]Or you can use this:[COLOR=#0000ff]

[/COLOR][COLOR=#0000ff]=IF(ROW()-ROW($B$5)>MONTH($B$2)-MONTH($B$1),"",TEXT(EOMONTH($B$1,ROW()-ROW($B$5)),"mmm"))[/COLOR]

2) Put the big array formula (use Ctrl+Shift+Enter to enter the formula) below in B5 in the sheet Main and copy down until B7 (like you said: only 03 months):

Code: 
[COLOR=#0000ff]=IF(A5="","",
IFERROR(SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),1),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),2),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),3),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),4),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),5),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),6),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),7),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),8),"")&
IFERROR(", "&SMALL(IF(Dates>=$B$1,IF(Dates<=$B$2,IF(Holis<>0,IF(MONTH(Dates)=MONTH(A5&YEAR($B$1)),DAY(Dates))))),9),""))[/COLOR]

Markmzz
Click to expand...
 
Upvote 0
Last edited:
Upvote 0
markmzz said:
Here is your file with the formulas

https://1drv.ms/x/s!AvFxmZVmmL9Sh3kplUtw5aSp2Kzp

Do some tests.

By the way, your file is .xlsx format.

Markmzz
Click to expand...


Dear Markmzz,

Your solution worked like a charm. Thanks a lot. Sorry to bother you but can you please provide any alternative so that it works with .xls files too as the "big array formula" is not allowed in .xls formats due to nesting restrictions.

Thanks again.
 
Upvote 0
mukulesh said:
Dear Markmzz,

Your solution worked like a charm. Thanks a lot. Sorry to bother you but can you please provide any alternative so that it works with .xls files too as the "big array formula" is not allowed in .xls formats due to nesting restrictions.

Thanks again.
Click to expand...

Dear Mukulesh,

I'm sorry, but I didn't know how to do that (in this case - .xls file) in one cell without help columns (like Wideboydixon's suggestion in post #14).
​

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Display Dates (minus weekends and holidays) Using TODAY() Function
Replies
5
Views
358
Asbestos_Jen
Asbestos_Jen
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
3K
felixstraube
F
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
2K
Romy
R
Y
  • Question Question
IF AND dates calculation
Replies
4
Views
158
yorkam
Y
C
  • Solved
NETWORKDAYS.INTL holiday question
Replies
9
Views
775
Fluff
Fluff

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top