How to get Holidays in cell/s? (Find explaination in details)
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
|