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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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.

[TABLE="class: grid, width: 308"]
<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]10[/TD]
[TD]ter-09/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]qua-10/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]qui-11/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]sex-12/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]sáb-13/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]dom-14/01/2018[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]seg-15/01/2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]****************[/TD]
[TD]*********[/TD]
[TD]****[/TD]
[TD]*******[/TD]
[/TR]
</tbody>[/TABLE]


[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"]01/jan/18[/TD]
[TD][/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]End[/TD]
[TD="align: right"]31/dez/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]1, 7, 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, 25, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]abr[/TD]
[TD]1, 8, 15, 21, 22, 29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]mai[/TD]
[TD]1, 6, 13, 20, 27, 31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]jun[/TD]
[TD]3, 10, 17, 24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]jul[/TD]
[TD]1, 8, 15, 22, 29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]ago[/TD]
[TD]5, 12, 19, 26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]set[/TD]
[TD]2, 7, 9, 16, 23, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]out[/TD]
[TD]7, 12, 14, 21, 28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]nov[/TD]
[TD]2, 4, 11, 15, 18, 25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]dez[/TD]
[TD]2, 9, 16, 23, 25, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]********[/TD]
[TD]****************[/TD]
[TD]***[/TD]
[TD]******[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
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
 
Last edited:
Upvote 0
If you have Excel 2016 then you can use TEXTJOIN something 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
Calendar



Book1
AB
1Start Date:01-Jan-2018
2End Date:31-Mar-2018
3
4MonthsHolidays
5Jan7,14,21,26,28
6Feb4,11,18,25
7Mar4,11,18,25
8
9
10
11
12
13
14
15
16
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{=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
 
Upvote 0
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),""))

[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"]01/jan/18[/TD]
[TD][/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]End[/TD]
[TD="align: right"]31/mar/19[/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="align: right"]jan/2018[/TD]
[TD]1, 7, 14, 21, 28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]fev/2018[/TD]
[TD]4, 11, 13, 18, 25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]mar/2018[/TD]
[TD]4, 11, 18, 25, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]abr/2018[/TD]
[TD]1, 8, 15, 21, 22, 29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]mai/2018[/TD]
[TD]1, 6, 13, 20, 27, 31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]jun/2018[/TD]
[TD]3, 10, 17, 24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]jul/2018[/TD]
[TD]1, 8, 15, 22, 29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]ago/2018[/TD]
[TD]5, 12, 19, 26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]set/2018[/TD]
[TD]2, 7, 9, 16, 23, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]out/2018[/TD]
[TD]7, 12, 14, 21, 28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]nov/2018[/TD]
[TD]2, 4, 11, 15, 18, 25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]dez/2018[/TD]
[TD]2, 9, 16, 23, 25, 30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]jan/2019[/TD]
[TD]1, 6, 13, 20, 27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]fev/2019[/TD]
[TD]3, 10, 17, 24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]mar/2019[/TD]
[TD]3, 5, 10, 17, 24, 31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]********[/TD]
[TD]****************[/TD]
[TD]***[/TD]
[TD]******[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

Did you try my suggestion of post #2?

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"]25/fev/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]********[/TD]
[TD]****************[/TD]
[TD]***[/TD]
[TD]******[/TD]
[/TR]
</tbody>[/TABLE]

And 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]


Markmzz
 
Upvote 0
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.
Click to expand...

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​
 
Upvote 0
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​
Click to expand...


The formula on A5 is returning blank (#NUM error when IFERROR is removed).
 
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,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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