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
markmzz said:
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
Click to expand...


Please don't be sorry. You have helped me a lot and I'll remain ever grateful. By the way is it possible to split the formula in more than one cell and then concatenate the results to the destination cell?
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
mukulesh said:
Please don't be sorry. You have helped me a lot and I'll remain ever grateful. By the way is it possible to split the formula in more than one cell and then concatenate the results to the destination cell?
Click to expand...

Yes, look at the Wideboydixon's suggestion in post #14.

Here is Wideboydixon's suggestion with a small modification in the second formula:

In D3:

=MID($E3&$F3&$G3&$H3&$I3&$J3&$K3&$L3&$M3&N3&O3,2,1024)

In E3 (Array Formula - use Ctrl+Shift+Enter) and copy to the right

=IF($D2="","",IFERROR(", "&SMALL(IF(Dates>=$C3,IF(Dates<=$C5,IF(Holis<>0,IF(MONTH(Dates)=MONTH($D2&YEAR($C3)),DAY(Dates))))),COLUMNS($E3:E3)),""))

or

=IF($D2="","",IF(SUMPRODUCT(--(Dates>=$C3),--(Dates<=$C5),--(Holis<>0),--(MONTH(Dates)=MONTH($D2&YEAR($C3))))>=COLUMNS($E3:E3),
", "&SMALL(IF(Dates>=$C3,IF(Dates<=$C5,IF(Holis<>0,IF(MONTH(Dates)=MONTH($D2&YEAR($C3)),DAY(Dates))))),COLUMNS($E3:E3)),""))

Markmzz​
 
Last edited:
Upvote 0
My suggestion uses just one helper column. I've used column E in 'Sheet1'. Can be hidden after formulas entered.

I'd suggest that you test the following in a copy of your workbook.

However, my suggestion also includes a change to your current formula in column B of Sheet1. It seems to be to be no benefit to make all those non-holiday cells equal to 0 and then use Conditional formatting to hide the 0 values. Why not change the formula to
=IF(A2="","-",(IF(WEEKDAY(A2)=1,"Sunday",IF(A2=FLOOR(DATE(YEAR(A2),MONTH(A2),14),7),"2nd Saturday",IF(A2=FLOOR(DATE(YEAR(A2),MONTH(A2),28),7),"4th Saturday","")))))

You can then delete the Conditional Formatting rule for 0 values and change the 'grey' rule form Cell value not equal to =0 to Cell value not equal to =""
(Note: You have said you want this to work in .xls format If that is to be actually using Excel 2003 or earlier, your conditional formatting will fail as only 3 formats were allowed in those versions)

Now in Cell E2 of Sheet1 put the following formula & copy right down to the end of the data. The correct results will not display until you have copied it all the way down (or at least past the 'End' date)

Excel Workbook
ABCDE
1DateHoliday
201/Jan/2018 7 13 14 15 21 23 26 27 28 31
302/Jan/20187 13 14 15 21 23 26 27 28 31
403/Jan/20187 13 14 15 21 23 26 27 28 31
504/Jan/20187 13 14 15 21 23 26 27 28 31
605/Jan/20187 13 14 15 21 23 26 27 28 31
706/Jan/20187 13 14 15 21 23 26 27 28 31
807/Jan/2018Sunday7 13 14 15 21 23 26 27 28 31
908/Jan/201813 14 15 21 23 26 27 28 31
1009/Jan/201813 14 15 21 23 26 27 28 31
1110/Jan/201813 14 15 21 23 26 27 28 31
1211/Jan/201813 14 15 21 23 26 27 28 31
1312/Jan/201813 14 15 21 23 26 27 28 31
1413/Jan/20182nd Saturday13 14 15 21 23 26 27 28 31
1514/Jan/2018Holiday14 15 21 23 26 27 28 31
1615/Jan/2018Holiday15 21 23 26 27 28 31
1716/Jan/201821 23 26 27 28 31
1817/Jan/201821 23 26 27 28 31
Sheet1
#VALUE!
</td></tr></table></td></tr></table>


The in 'Main'
A5 is stand-alone
A6 and B5 copied down as far as you might need.

Excel Workbook
AB
1Start:01/Jan/2018
2End:31/Mar/2018
3
4MonthsHolidays
5Jan7, 13, 14, 15, 21, 23, 26, 27, 28, 31
6Feb4, 10, 11, 18, 24, 25
7Mar1, 4, 10, 11, 18, 24, 25, 30
8
Main
#VALUE!
</td></tr></table></td></tr></table>
 
Upvote 0
Forgot to mention 2 things about the change to formula in column B of Sheet1
1. You will need to record & re-enter the manually-entered holidays in that column.
2. The main reason for the suggested change was to make the formulas in helper column E simpler.
 
Upvote 0
Peter_SSs said:
Forgot to mention 2 things about the change to formula in column B of Sheet1
1. You will need to record & re-enter the manually-entered holidays in that column.
2. The main reason for the suggested change was to make the formulas in helper column E simpler.
Click to expand...

Hi Peter,

I think that the formula below isn't ok:

=IF(A2<=End,IF(OR(B2="",A2<start)< span="">,"",DAY(A2)&" ")</start)<>&IF(DAY(A2)>DAY(A3),"",E3),"")​

or I'm wrong?

Markmzz
 
Upvote 0
mukulesh said:
Please don't be sorry. You have helped me a lot and I'll remain ever grateful. By the way is it possible to split the formula in more than one cell and then concatenate the results to the destination cell?
Click to expand...

Hi!

Here is your file (.xls) with some suggestions (Markmzz's, Peter's and Wideboydixon's suggestions):

=IF(A2="","-",(IF(WEEKDAY(A2)=1,"Sunday",
IF(DAY(A2)=15-WEEKDAY(A2-DAY(A2)+1),"2nd Saturday",
IF(DAY(A2)=29-WEEKDAY(A2-DAY(A2)+1),"4th Saturday",
IF(COUNTIF(Feriados,A2),VLOOKUP(A2,TabFer,2,0),""))))))

=IF(MEDIAN(A2,Start,End)=A2,IF(B2="","",DAY(A2)&" ")&IF(DAY(A2)>DAY(A3),"",E3),"")

=TEXT(Start,"mmm")

=IF(A5="","",SUBSTITUTE(TRIM(VLOOKUP(DATEVALUE(1&A5),Sheet1!A$1:E$366,5))," ",", "))

=IF(SUMPRODUCT(--(Dates>=C3),--(Dates<=DATE(YEAR(C3),MONTH(C3)+1,0)),--(Holis<>"")),TEXT(C3,"mmmm"),
IF(SUMPRODUCT(--(Dates>=C5-DAY(C5)+1),--(Dates<=C5),--(Holis<>"")),TEXT(C5,"mmmm"),""))

=MID($E3&$F3&$G3&$H3&$I3&$J3&$K3&$L3&$M3&N3&O3,2,1024)

Array Formula (use Ctrl+Shift+Enter to enter the formula)

=IF($D2="","",IF(SUMPRODUCT(--(Dates>=$C3),--(Dates<=$C5),--(Holis<>""),--(MONTH(Dates)=MONTH($D2&YEAR($C3))))>=COLUMNS($E3:E3),
", "&SMALL(IF((Dates>=$C3)*(Dates<=$C5)*(Holis<>"")*(MONTH(Dates)=MONTH($D2&YEAR($C3))),DAY(Dates)),COLUMNS($E3:E3)),""))

=IF((TEXT(C5,"mmmm")<>D2)*SUMPRODUCT(--(Dates>=C5-DAY(C5)+1),--(Dates<=C5),--(Holis<>"")),TEXT(C5,"mmmm"),"")

Link for the file: https://1drv.ms/x/s!AvFxmZVmmL9Sh3oKif31mHyM2xoH

Do some tests.

I hope this helps.

Markmzz
 
Upvote 0
markmzz said:
I think that the formula below isn't ok:

=IF(A2<=End,IF(OR(B2="",A2<start)< span="">,"",DAY(A2)&" ")</start)<>&IF(DAY(A2)>DAY(A3),"",E3),"")​

or I'm wrong?
Click to expand...
No, you are perfectly correct Mark, thanks for picking it up. I had the formula I wanted in my sheet but it was the old problem of the forum cutting off text, particularly formulas, when this happens. :eek:

So here is Sheet1 from post #23 again, this time with the full formula showing.

Excel Workbook
ABCDE
1DateHoliday
201/Jan/2018 7 13 14 15 21 23 26 27 28 31
302/Jan/20187 13 14 15 21 23 26 27 28 31
403/Jan/20187 13 14 15 21 23 26 27 28 31
504/Jan/20187 13 14 15 21 23 26 27 28 31
605/Jan/20187 13 14 15 21 23 26 27 28 31
706/Jan/20187 13 14 15 21 23 26 27 28 31
807/Jan/2018Sunday7 13 14 15 21 23 26 27 28 31
908/Jan/201813 14 15 21 23 26 27 28 31
1009/Jan/201813 14 15 21 23 26 27 28 31
1110/Jan/201813 14 15 21 23 26 27 28 31
1211/Jan/201813 14 15 21 23 26 27 28 31
1312/Jan/201813 14 15 21 23 26 27 28 31
1413/Jan/20182nd Saturday13 14 15 21 23 26 27 28 31
1514/Jan/2018Holiday14 15 21 23 26 27 28 31
1615/Jan/2018Holiday15 21 23 26 27 28 31
1716/Jan/201821 23 26 27 28 31
1817/Jan/201821 23 26 27 28 31
Sheet1
#VALUE!
</td></tr></table></td></tr></table>


Sheet 'Main' and its formulas posted correctly in post #23 so remian unchanged.
 
Upvote 0
Hi!

Another way (with the file .xls ). With a small modification in your layout of the sheet Desired Result and a small modification in Peter's suggestion:

Lets go:

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))
TabHolis - Refers to: =Sheet1!$A$3:$I$366
TabFer - Refers to: =Feriados!$A$2:$A$373
Start - Refers to: =Main!$B$1
End - Refers to: =Main!$B$2
Start_1 - Refers to: ='Desired Result'!$B$3
End_1 - Refers to: ='Desired Result'!$D$3

2) After that, put the formulas below in the sheet Sheet1:

In B2 and copy down
=IF(A2="","-",IF(WEEKDAY(A2)=1,"Sunday",
IF(DAY(A2)=15-WEEKDAY(A2-DAY(A2)+1),"2nd Saturday",
IF(DAY(A2)=29-WEEKDAY(A2-DAY(A2)+1),"4th Saturday",
IF(COUNTIF(INDEX(TabFer,,1),A2),VLOOKUP(A2,TabFer,2,0),"")))))

In E2 and copy to the right until I2 and down until I366
=TRIM(IF(MEDIAN($A2,OFFSET(Start_1,(COLUMNS($E2:E2)-1)*4,),OFFSET(End_1,(COLUMNS($E2:E2)-1)*4,))=$A2,
IF($B2="","",DAY($A2)&" ")&IF(DAY($A2)>DAY($A3),"",E3),""))

in K3 and copy down until K366
=TRIM(IF(MEDIAN($A2,Start,End)=$A2,IF($B2="","",DAY($A2)&" ")&IF(DAY($A2)>DAY($A3),"",K3),""))

3) Then, put the formulas below in the sheet Main:

In A5 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy down
=IF(ISERROR(INDEX(TEXT(Dates,"mmm"),MATCH(0,COUNTIF(A$4:A4,TEXT(Dates,"mmm"))+
(Dates<$B$1)+(Dates>$B$2)+(Holis=""),0))),"",
INDEX(TEXT(Dates,"mmm"),MATCH(0,COUNTIF(A$4:A4,TEXT(Dates,"mmm"))+
(Dates<$B$1)+(Dates>$B$2)+(Holis=""),0)))

In B5 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy down
=IF(A5="","",SUBSTITUTE(VLOOKUP(SMALL(IF((Dates>=$B$1)*(Dates<=$B$2)*(Holis<>"")*
(MONTH(Dates)=MONTH(1&$A5)),Dates),1),Sheet1!$A$2:$K$366,11)," ",", "))

4) Now, put the formulas below in the sheet Desired Result:

In C3 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy down
=IF(ISERROR(INDEX(TEXT(Dates,"mmmm"),MATCH(0,COUNTIF(OFFSET(C4,-MOD(ROWS(C$4:C4),4),):C3,TEXT(Dates,"mmmm"))+
(Dates<lookup(8^7,c$3:c3))+(dates>LOOKUP(8^7,D$3:D3))+(Holis=""),0))),"",</lookup(8^7,c$3:c3))+(dates>
INDEX(TEXT(Dates,"mmmm"),MATCH(0,COUNTIF(OFFSET(C4,-MOD(ROWS(C$4:C4),4),):C3,TEXT(Dates,"mmmm"))+
(Dates<lookup(8^7,c$3:c3))+(dates>LOOKUP(8^7,D$3:D3))+(Holis=""),0)))</lookup(8^7,c$3:c3))+(dates><lookup(8^7,c$3:c3))+(dates>
</lookup(8^7,c$3:c3))+(dates>
In D3 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy down
=IF(C4="","",SUBSTITUTE(VLOOKUP(SMALL(IF((Dates>=LOOKUP(8^7,C$3:C3))*(Dates<=LOOKUP(8^7,D$3:D3))*(Holis<>"")*
(MONTH(Dates)=MONTH(1&C4)),Dates),1),TabHolis,4+CEILING(ROWS(D$4:D4),4)/4)," ",", "))


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 21"]
[/TD]
[TD="width: 56"]A[/TD]
[TD="width: 99"]B[/TD]
[TD="width: 77"]C[/TD]
[TD="width: 157"]D[/TD]
[TD="width: 21"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sl. No.[/TD]
[TD]Places of Visit[/TD]
[TD]Holidays[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1
[/TD]
[TD]Place 1[/TD]
[TD]01/01/2018[/TD]
[TD]31/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]janeiro[/TD]
[TD]1, 7, 13, 14, 21, 27, 28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]fevereiro[/TD]
[TD]4, 10, 11, 13, 18, 24, 25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]março[/TD]
[TD]4, 10, 11, 18, 24, 25, 30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]2[/TD]
[TD]Place 2[/TD]
[TD]01/02/2018[/TD]
[TD]31/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]fevereiro[/TD]
[TD]4, 10, 11, 13, 18, 24, 25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]março[/TD]
[TD]4, 10, 11, 18, 24, 25, 30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]3[/TD]
[TD]Place 3[/TD]
[TD]26/01/2018[/TD]
[TD]05/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]janeiro[/TD]
[TD]27, 28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]fevereiro[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]4[/TD]
[TD]Place 4[/TD]
[TD]01/03/2018[/TD]
[TD]04/03/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]março[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]5[/TD]
[TD]Place 5[/TD]
[TD]26/02/2018[/TD]
[TD]28/02/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]*********************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Do some tests.

Markmzz
 
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