Like Bob, I'm a bit confused here. Can you lay out the rules for when a day
should NOT count in the workdays total?

This is just for example, not based on anything provided so far. Perhaps in
terms like these:

Sunday Never counts, holiday or not.
If a holiday falls on a Sunday, then the following Monday does not count
(except Easter which is always observed on the Sunday)
If a holiday falls on a Saturday, then the Friday before is the day that
does not count.

But I think we may have some problem with the 6-day week? If a Holiday
falls on a Friday/Saturday, how are those handled? The actual day is the
holiday? The day after? The day before? Both?

And have you accepted Bob's formula as being correct and providing the
values you need? It sounds that way to me, I just want to be sure. And
don't forget Bob's question.

All in all I think this is an interesting exercise, and is solvable with
Excel, but I think we've kind of gotten twisted up in what the rules are. Or
at least I have for the moment.

"dribler2" wrote:

Im in deep trouble, very very sorry,

i forgot to tell you that there will be work on monday after easter sunday,
meaning the previous count should add 2. (1 day for 2007 and 1 day for 2008).

current workday count is 597+2=599
from 1/2/2007 to 1/2/2009 (inclusive)

my mistake

thanks for the right formula
dribler2




"JLatham" wrote:

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