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

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$1:D1)<=INDEX(FREQUENCY(Hdays,A$1:A$2-{1;0}),2),SMALL(IF((Hdays=start)*(Hdays<=end),Hda ys),ROWS(D$1:D1)),"")

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



--
Biff
Microsoft Excel MVP


"Nilay Excel 2003" wrote in
message ...
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

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


Nilay



"T. Valko" wrote:

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$1:D1)<=INDEX(FREQUENCY(Hdays,A$1:A$2-{1;0}),2),SMALL(IF((Hdays=start)*(Hdays<=end),Hda ys),ROWS(D$1:D1)),"")

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



--
Biff
Microsoft Excel MVP


"Nilay Excel 2003" wrote in
message ...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Nilay Excel 2003" wrote in
message ...
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" wrote:

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$1:D1)<=INDEX(FREQUENCY(Hdays,A$1:A$2-{1;0}),2),SMALL(IF((Hdays=start)*(Hdays<=end),Hda ys),ROWS(D$1:D1)),"")

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



--
Biff
Microsoft Excel MVP


"Nilay Excel 2003" wrote in
message ...
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