How to get Holidays in cell/s? (Find explaination in details)

[Nilay Excel 2003]

Dear reader, I am again trying to explain in details (My Project name: Leave
Application)

I have Data for an Excel Sheet as:-
1) Cell A1 : Start Date (e.g. 12 Dec 2007)
2) Cell A2 : End Date & (e.g. 31 Dec 2007)
3) Total Holidays of year : Filled in Colomn B (e.g. List of total 15
Holidays in year 2007 :26 Jan, 2 Oct,------, 14 Dec, 25 Dec)

I got the results for calculation for "Total no. of leaves including
Holidays= "cell A3" i.e. 15 Days using "networkdays" function (modified for 6
working days as per my requirnment) within start & End dates
Now I want a function which can fill the Holidays dates from Start
date (A1) & End dates (A2) in the cell/s named as "And Holidays are on: 14
Dec 2007, 25 Dec 2007" from the Holiday list.
If you got the clearity for the problem, please reply for solution,
else reply. I will be thankful to you.

Nilay

 

[T. Valko]

Try this array formula** :

Assumptions:

A1 = start date = named cell = start
A2 = end date = named cell = end
B1:B15 = list of holiday dates = named range = hdays

Enter this array formula** in cell D1 and copy down to a number of cells
that is equal to the number of holiday dates in your holiday list. In this
example, that would be 15 cells.

=IF(ROWS(D$11)<=INDEX(FREQUENCY(Hdays,A$1:A$2-{1;0}),2),SMALL(IF((Hdays>=start)*(Hdays<=end),Hdays),ROWS(D$11)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Nilay Excel 2003]

Dear T. Valko,
It’s working in very correct manner & I got the exact result
that’s needed.
Really Great helpline I got from you & Greg Wilson also for my project.
Thanks again.


Nilay

 

[T. Valko]

You're welcome. Thanks for the feedback!