basic formula for lookup

D

dribler2

for anyone to help
I am still uncapable of making a short lookup formula something like this

=lookup(date1:date2, holidays datelist)
i want to count how many holidays are included between 2 given dates.

thanks for kind help
driller
 
B

Bob Phillips

=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(start_date&":"&end_date)),holiday
s,0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

dribler2

Hi Sir Philippe,
thanks for the link, yet I cannot find the solution..

I need a match or a lookup of floating dates within the 2 given dates.

e.g.
date given 1 = 01/Dec/06
date given 2 = 20/Feb/06

holiday list
08/Dec/06 <
31/Dec/06 <
01/Jan/06 <
20/Feb/06 <
01/May/06

then the desired count of holiday = 4

i hope i have explained it clearly...

i am so confused because my boss request me with a single formula, even a
long formula will do

happy holidays
driller
 
D

dribler2

Hi Gary''s Student

please explain how to set this as an add-in.

thanks again
dribler2
 
D

dribler2

thanks

Yah no problem, i place it my personal.xls it do works without sweat...good
job u have done for this question.

In my 6 day a week schedule, my boss real problem now is based on the real
world wherein if holiday falls on sunday, then monday has to be a holiday
too, same goes for holiday that falls on friday then saturday has to be
considered as holiday too...following local labor code...

i hope u can say something about this...in this forum or thru other means.

happy holidays
romelsb
 
G

Gary''s Student

Lets put some dates in A1 thru A2:

07/04/2006 - Tue
07/04/2005 - Mon
07/04/2004 - Sun
07/04/2003 - Fri
07/04/2002 - Thu
07/04/2001 - Wed
07/04/2000 - Tue
07/04/1999 - Sun
07/04/1998 - Sat
07/04/1997 - Fri
07/04/1996 - Thu
07/04/1995 - Tue
07/04/1994 - Mon
07/04/1993 - Sun
07/04/1992 - Sat
07/04/1991 - Thu
07/04/1990 - Wed
07/04/1989 - Tue
07/04/1988 - Mon
07/04/1987 - Sat

In B1 enter:
=IF(WEEKDAY(A1)=1,A1+1,IF(WEEKDAY(A1)=6,A1+1,A1))
and copy down.

This results in:

07/04/2006 - Tue 07/04/2006 - Tue
07/04/2005 - Mon 07/04/2005 - Mon
07/04/2004 - Sun 07/05/2004 - Mon
07/04/2003 - Fri 07/05/2003 - Sat
07/04/2002 - Thu 07/04/2002 - Thu
07/04/2001 - Wed 07/04/2001 - Wed
07/04/2000 - Tue 07/04/2000 - Tue
07/04/1999 - Sun 07/05/1999 - Mon
07/04/1998 - Sat 07/04/1998 - Sat
07/04/1997 - Fri 07/05/1997 - Sat
07/04/1996 - Thu 07/04/1996 - Thu
07/04/1995 - Tue 07/04/1995 - Tue
07/04/1994 - Mon 07/04/1994 - Mon
07/04/1993 - Sun 07/05/1993 - Mon
07/04/1992 - Sat 07/04/1992 - Sat
07/04/1991 - Thu 07/04/1991 - Thu
07/04/1990 - Wed 07/04/1990 - Wed
07/04/1989 - Tue 07/04/1989 - Tue
07/04/1988 - Mon 07/04/1988 - Mon
07/04/1987 - Sat 07/04/1987 - Sat


Now each Sunday has been moved to Monday and each Friday has been moved to
Saturday
 
D

daddylonglegs

Wouldn't it be better to switch this around?

=SUMPRODUCT(--(holidays>=start_date),--(holidays<=end_date))
 
Top