With all formulas i gathered, i am still unable to do what my boss need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday fall on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MIN(end_date,start_date))*( holidays<=MAX(end_date,start_date)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
With all formulas i gathered, i am still unable to do what my boss need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday fall
on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4

Sir Bob,

here is my data
the holiday date list
'---------------------------
Monday, January 01, 2007
Thursday, February 01, 2007
Wednesday, February 14, 2007
Thursday, April 05, 2007
Friday, April 06, 2007
Sunday, April 08, 2007
Tuesday, May 01, 2007
Monday, May 28, 2007
Wednesday, August 01, 2007
Friday, September 14, 2007
Friday, September 14, 2007
Friday, November 02, 2007
Friday, December 07, 2007
Tuesday, December 25, 2007
Tuesday, January 01, 2008
Friday, February 01, 2008
Wednesday, January 30, 2008
Thursday, March 20, 2008
Friday, March 21, 2008
Sunday, March 23, 2008
Thursday, May 01, 2008
Tuesday, May 27, 2008
Friday, August 01, 2008
Monday, September 15, 2008
Monday, September 15, 2008
Monday, November 03, 2008
Monday, December 08, 2008
Thursday, December 25, 2008
Thursday, January 01, 2009
Monday, February 02, 2009
'---------------------------
start_date = 1/2/2007
end_date = 1/2/2009
'---------------------------
from the long list of workday series numbers, per criteria...
the result is 597....

maybe my boss table has a problem, ill try to count it visually.
happy holidays
been dribled2





"Bob Phillips" wrote:

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MIN(end_date,start_date))*( holidays<=MAX(end_date,start_date)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
With all formulas i gathered, i am still unable to do what my boss need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday fall
on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4

I used a somewhat brute force method to perhaps double check the numbers and
I come up with
732 days between 1/2/2007 1/2/2009 (inclusive)

During that period it appears that there are 137 days that meet the
exclude-it-date, so I end up with 596 (732-137+1) days that have to be paid
for.
File is he
http://www.jlathamsite.com/uploads/N...aWorkdays1.xls

By the way - in your list there are two dates that are duplicated:
Friday, September 14, 2007 is listed twice
Monday, September 15, 2008 is also listed twice

I think I probably know why, but thought I'd call that to your attention.

"dribler2" wrote:

Sir Bob,

here is my data
the holiday date list
'---------------------------
Monday, January 01, 2007
Thursday, February 01, 2007
Wednesday, February 14, 2007
Thursday, April 05, 2007
Friday, April 06, 2007
Sunday, April 08, 2007
Tuesday, May 01, 2007
Monday, May 28, 2007
Wednesday, August 01, 2007
Friday, September 14, 2007
Friday, September 14, 2007
Friday, November 02, 2007
Friday, December 07, 2007
Tuesday, December 25, 2007
Tuesday, January 01, 2008
Friday, February 01, 2008
Wednesday, January 30, 2008
Thursday, March 20, 2008
Friday, March 21, 2008
Sunday, March 23, 2008
Thursday, May 01, 2008
Tuesday, May 27, 2008
Friday, August 01, 2008
Monday, September 15, 2008
Monday, September 15, 2008
Monday, November 03, 2008
Monday, December 08, 2008
Thursday, December 25, 2008
Thursday, January 01, 2009
Monday, February 02, 2009
'---------------------------
start_date = 1/2/2007
end_date = 1/2/2009
'---------------------------
from the long list of workday series numbers, per criteria...
the result is 597....

maybe my boss table has a problem, ill try to count it visually.
happy holidays
been dribled2





"Bob Phillips" wrote:

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MIN(end_date,start_date))*( holidays<=MAX(end_date,start_date)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
With all formulas i gathered, i am still unable to do what my boss need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday fall
on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4

By my calculation the result is 600. I excluded one each of the duplicate
dates in the holidays list. Also note that there are 2 dates in the holiday
list that are outside of the date range.

This doesn't make any sense (to me):

Excluding sundays and holidays
AND next monday if holiday falls on sunday,
AND saturday if holiday fall on friday...

Since both Friday and Saturday are regular workdays excluding Saturday for a
Friday holiday is redundant.

=SUM(INT((WEEKDAY(start-{1,2,3,4,5,6},2)+end-start)/7))-SUMPRODUCT(--(holidays=start),--(holidays<=end))-SUMPRODUCT(--(WEEKDAY(holidays,2)=7),--(holidays=start),--(holidays<=end))

Biff

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I used a somewhat brute force method to perhaps double check the numbers
and
I come up with
732 days between 1/2/2007 1/2/2009 (inclusive)

During that period it appears that there are 137 days that meet the
exclude-it-date, so I end up with 596 (732-137+1) days that have to be
paid
for.
File is he
http://www.jlathamsite.com/uploads/N...aWorkdays1.xls

By the way - in your list there are two dates that are duplicated:
Friday, September 14, 2007 is listed twice
Monday, September 15, 2008 is also listed twice

I think I probably know why, but thought I'd call that to your attention.

"dribler2" wrote:

Sir Bob,

here is my data
the holiday date list
'---------------------------
Monday, January 01, 2007
Thursday, February 01, 2007
Wednesday, February 14, 2007
Thursday, April 05, 2007
Friday, April 06, 2007
Sunday, April 08, 2007
Tuesday, May 01, 2007
Monday, May 28, 2007
Wednesday, August 01, 2007
Friday, September 14, 2007
Friday, September 14, 2007
Friday, November 02, 2007
Friday, December 07, 2007
Tuesday, December 25, 2007
Tuesday, January 01, 2008
Friday, February 01, 2008
Wednesday, January 30, 2008
Thursday, March 20, 2008
Friday, March 21, 2008
Sunday, March 23, 2008
Thursday, May 01, 2008
Tuesday, May 27, 2008
Friday, August 01, 2008
Monday, September 15, 2008
Monday, September 15, 2008
Monday, November 03, 2008
Monday, December 08, 2008
Thursday, December 25, 2008
Thursday, January 01, 2009
Monday, February 02, 2009
'---------------------------
start_date = 1/2/2007
end_date = 1/2/2009
'---------------------------
from the long list of workday series numbers, per criteria...
the result is 597....

maybe my boss table has a problem, ill try to count it visually.
happy holidays
been dribled2





"Bob Phillips" wrote:

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MIN(end_date,start_date))*( holidays<=MAX(end_date,start_date)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
With all formulas i gathered, i am still unable to do what my boss
need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday
fall
on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4

I also came up with 600 at one point also, I think (if memory serves me
right). That was before I went through the additional rules regarding
holidays that fall on Saturday/Sunday. We may need to get the OP to clarify
that.

Initially, in another discussion, he stated that their weekend holiday rules
followed U.S. 'rules': Holiday falls on Saturday, it is observed on Friday;
if it falls on Sunday it is observed on Monday. But there may be an
exception for something like Easter which will always fall on Sunday and it
would seem somewhat unreasonable to observe it on Monday.

The fact that he seems to have a 6-day workweek, vs 5-day workweek may play
into why the rules are laid out the we he stated above. I used the rules
above to come up with my numbers, so if the rule about Friday/Saturday is in
error, so may my values.

I provided the 4-column, step by step 'solution' just as a check against
more compact formulas like you and Bob Phillips have provided, not as a
solution for the OP to actually use.

Like you, I noted that a couple of the dates in the list were outside of the
range of dates he indicated are to be considered - my 'check' just includes
the dates to be considered, so a lookup for 1/1/2007 won't find a result and
won't count as one of the unpaid days.

"T. Valko" wrote:

By my calculation the result is 600. I excluded one each of the duplicate
dates in the holidays list. Also note that there are 2 dates in the holiday
list that are outside of the date range.

This doesn't make any sense (to me):

Excluding sundays and holidays
AND next monday if holiday falls on sunday,
AND saturday if holiday fall on friday...

Since both Friday and Saturday are regular workdays excluding Saturday for a
Friday holiday is redundant.

=SUM(INT((WEEKDAY(start-{1,2,3,4,5,6},2)+end-start)/7))-SUMPRODUCT(--(holidays=start),--(holidays<=end))-SUMPRODUCT(--(WEEKDAY(holidays,2)=7),--(holidays=start),--(holidays<=end))

Biff

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
I used a somewhat brute force method to perhaps double check the numbers
and
I come up with
732 days between 1/2/2007 1/2/2009 (inclusive)

During that period it appears that there are 137 days that meet the
exclude-it-date, so I end up with 596 (732-137+1) days that have to be
paid
for.
File is he
http://www.jlathamsite.com/uploads/N...aWorkdays1.xls

By the way - in your list there are two dates that are duplicated:
Friday, September 14, 2007 is listed twice
Monday, September 15, 2008 is also listed twice

I think I probably know why, but thought I'd call that to your attention.

"dribler2" wrote:

Sir Bob,

here is my data
the holiday date list
'---------------------------
Monday, January 01, 2007
Thursday, February 01, 2007
Wednesday, February 14, 2007
Thursday, April 05, 2007
Friday, April 06, 2007
Sunday, April 08, 2007
Tuesday, May 01, 2007
Monday, May 28, 2007
Wednesday, August 01, 2007
Friday, September 14, 2007
Friday, September 14, 2007
Friday, November 02, 2007
Friday, December 07, 2007
Tuesday, December 25, 2007
Tuesday, January 01, 2008
Friday, February 01, 2008
Wednesday, January 30, 2008
Thursday, March 20, 2008
Friday, March 21, 2008
Sunday, March 23, 2008
Thursday, May 01, 2008
Tuesday, May 27, 2008
Friday, August 01, 2008
Monday, September 15, 2008
Monday, September 15, 2008
Monday, November 03, 2008
Monday, December 08, 2008
Thursday, December 25, 2008
Thursday, January 01, 2009
Monday, February 02, 2009
'---------------------------
start_date = 1/2/2007
end_date = 1/2/2009
'---------------------------
from the long list of workday series numbers, per criteria...
the result is 597....

maybe my boss table has a problem, ill try to count it visually.
happy holidays
been dribled2





"Bob Phillips" wrote:

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MIN(end_date,start_date))*( holidays<=MAX(end_date,start_date)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
With all formulas i gathered, i am still unable to do what my boss
need.

my boss is never satisfied maybe this is the last he will ask me

something like this

given is a holiday date list in series

he gave me the first date
then he gave me another date

he want me to count the mon-sat workdays within the given dates.

Excluding sundays and holidays
AND next monday if holiday falls on sunday, AND saturday if holiday
fall
on
friday...

I hope he pays my extra worked-holidays

thanks for assistance
been dribled4