basic formula for lookup
 

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

basic formula for lookup
 

"dribler2" wrote in message
...
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


See http://www.j-walk.com/ss/excel/usertips/tip016.htm

basic formula for lookup
 

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

--
HTH

Bob Phillips

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

"dribler2" wrote in message
...
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

basic formula for lookup
 

"dribler2" wrote in message
...
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

Or if that was down see
http://www.dailydoseofexcel.com/arch...9/networkdays/

basic formula for lookup
 

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

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


"Philippe L. Balmanno" wrote:

"dribler2" wrote in message
...
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


See http://www.j-walk.com/ss/excel/usertips/tip016.htm

basic formula for lookup
 

Hi Sir Bob,

please set an example.

thanks again
dribler2

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"dribler2" wrote in message
...
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

basic formula for lookup
 

Hi Gary''s Student

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

thanks again
dribler2

"Gary''s Student" wrote:

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

I do not know.

i have never tried to create an add-in
--
Gary's Student


"dribler2" wrote:

Hi Gary''s Student

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

thanks again
dribler2

"Gary''s Student" wrote:

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

See:

http://support.microsoft.com/?id=211563

--
Gary's Student


"dribler2" wrote:

Hi Gary''s Student

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

thanks again
dribler2

"Gary''s Student" wrote:

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

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


"Gary''s Student" wrote:

See:

http://support.microsoft.com/?id=211563

--
Gary's Student


"dribler2" wrote:

Hi Gary''s Student

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

thanks again
dribler2

"Gary''s Student" wrote:

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

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
--
Gary's Student


"dribler2" wrote:

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


"Gary''s Student" wrote:

See:

http://support.microsoft.com/?id=211563

--
Gary's Student


"dribler2" wrote:

Hi Gary''s Student

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

thanks again
dribler2

"Gary''s Student" wrote:

How about:

Function counter2(d1 As Range, d2 As Range, hl As Range) As Integer

d1v = DateValue(d1.Value)
d2v = DateValue(d2.Value)

c = 0
For Each r In hl
dh = DateValue(r.Value)
c = c - (dh = d1v And dh <= d2v)
Next
counter2 = c
End Function

If A1 contains: 1/1/2006
and B1 contains:1/1/2007
and the holiday list is in C1 thru C5:
1/1/2006
1/1/2007
7/4/2006
1/18/2006
7/4/2007


Then =counter2(A1,B1,C1:C5)
returns 4 - the number of holidays between A1 and B1
--
Gary's Student


"dribler2" wrote:

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

basic formula for lookup
 

Wouldn't it be better to switch this around?

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"dribler2" wrote in message
...
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