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

  • Thread starter Nilay Excel 2003
  • Start date
N

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

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top