how to count easily

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

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

 

[Guest]

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 here:
http://www.jlathamsite.com/uploads/NicaraguaWorkdays1.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.

 

[T. Valko]

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

 

[Guest]

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.

 

[Bob Phillips]

According to my calcs there are 628 non-Sundays in that date-range, and
there are 24 valid dates in the holidays, giving 604 which is what the
formula gives me.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

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

 

[Bob Phillips]

Why 597?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

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.

 

[Guest]

Dear Sirs, sorry for a late reply
here is the correct list of holidays and in perfect series <increasing>
name as Holidays------------------------------
1-Jan-07 New Year's Day
1-Feb-07 Air Force Day
14-Feb-07 Ash Wednesday (start of Lent)
5-Apr-07 Holy Thursday
6-Apr-07 Holy Friday
8-Apr-07 Easter
1-May-07 Labor Day
27-May-07 Army Day
1-Aug-07 Fiesta Day
14-Sep-07 San Jacinto Day
15-Sep-07 Independence Day
2-Nov-07 All Souls Day
8-Dec-07 Immaculate Conception
25-Dec-07 Christmas
1-Jan-08 New Year's Day
30-Jan-08 Ash Wednesday (start of Lent)
1-Feb-08 Air Force Day
20-Mar-08 Holy Thursday
21-Mar-08 Holy Friday
23-Mar-08 Easter
1-May-08 Labor Day
27-May-08 Army Day
1-Aug-08 Fiesta Day
14-Sep-08 San Jacinto Day
15-Sep-08 Independence Day
2-Nov-08 All Souls Day
8-Dec-08 Immaculate Conception
25-Dec-08 Christmas
1-Jan-09 New Year's Day
1-Feb-09 Air Force Day
18-Feb-09 Ash Wednesday (start of Lent)
9-Apr-09 Holy Thursday
10-Apr-09 Holy Friday
12-Apr-09 Easter
1-May-09 Labor Day
27-May-09 Army Day
1-Aug-09 Fiesta Day
14-Sep-09 San Jacinto Day
15-Sep-09 Independence Day
2-Nov-09 All Souls Day
8-Dec-09 Immaculate Conception
25-Dec-09 Christmas
1-Jan-10 New Year's Day
1-Feb-10 Air Force Day
10-Feb-10 Ash Wednesday (start of Lent)
1-Apr-10 Holy Thursday
2-Apr-10 Holy Friday
4-Apr-10 Easter
1-May-10 Labor Day
27-May-10 Army Day
1-Aug-10 Fiesta Day
14-Sep-10 San Jacinto Day
15-Sep-10 Independence Day
2-Nov-10 All Souls Day
8-Dec-10 Immaculate Conception
25-Dec-10 Christmas
-------------------------------
Our guidelines for workscheduling code - just been drafted by my boss Today!
<for WorkDay scheduling of start date and finish date of our subcontractors>

1. if a holiday falls on friday, there is no work <with pay> on the
following day(sat).
2. if a holiday falls on Sunday, there is no work <with pay> on the
following day (mon).
3. there is a work on monday after easter sunday.
4. there is no work <with pay> if the day is between two holidays (e.g. see
31-Jan-2008,etc.)
5. there is no work <with pay> on monday if a holiday falls on tuesday.
(e.g. 31-Dec-2007, 26-May-2008, 07-Dec-2009, etc.)

i am really sorry for a late and confusing question. But this may be the
last shot. I may be drop-out of my workscheduling, very soon.

best wishes to all who help me
been dribled2

 

[T. Valko]

According to my calcs there are 628 non-Sundays in that date-range

That is correct.

The holiday list as posted has 30 entries.

2 of those dates are outside the date range:
Monday, January 01, 2007
Monday, February 02, 2009

start_date = 1/2/2007
end_date = 1/2/2009

2 entries have duplicates:

Friday, September 14, 2007
Friday, September 14, 2007
Monday, September 15, 2008
Monday, September 15, 2008

Excluding one each of the duplicates and the 2 entries outside the date
range, this leaves 26 valid holiday dates. Of those 26, 2 meet this
criteria:

AND next monday if holiday falls on sunday

So, 628 - (26-2) = 604.

My formula counted these entries twice: "AND next monday if holiday falls on
sunday"

So, inserting a set of ( ) fixes that:

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

This criteria is redundant and doesn't need any "special" consideration:

AND saturday if holiday fall on friday...

That's my final answer! (and I'm sticking to it!!!)

Biff

 

[T. Valko]

Excluding one each of the duplicates...

Means removing those entries from the holiday list.

Biff

 

[Guest]

happy new year sir Biff,
considering the first scenario,
my boss tried the formula, and he ask me to hthy.
He only give me two dates
date start = 2-jan-o7
date end = 4-apr-07 <@ 4months>
the manual count of workdays (inclusive of given dates) = 77 (same as your
formula's result)
yet
he gave again another headache date
date start = 2-jan-o7
date end = 8-apr-07 <sunday>
the manual count of workdays (inclusive of given dates) = 77
(but formula's result = 76)...i cant say anything to him....

after a debate again, i ask him to draft the guidelines...just today....
here is the correct list of holidays and in perfect series <increasing>
name as Holidays------------------------------
1-Jan-07 New Year's Day
1-Feb-07 Air Force Day
14-Feb-07 Ash Wednesday (start of Lent)
5-Apr-07 Holy Thursday
6-Apr-07 Holy Friday
8-Apr-07 Easter
1-May-07 Labor Day
27-May-07 Army Day
1-Aug-07 Fiesta Day
14-Sep-07 San Jacinto Day
15-Sep-07 Independence Day
2-Nov-07 All Souls Day
8-Dec-07 Immaculate Conception
25-Dec-07 Christmas
1-Jan-08 New Year's Day
30-Jan-08 Ash Wednesday (start of Lent)
1-Feb-08 Air Force Day
20-Mar-08 Holy Thursday
21-Mar-08 Holy Friday
23-Mar-08 Easter
1-May-08 Labor Day
27-May-08 Army Day
1-Aug-08 Fiesta Day
14-Sep-08 San Jacinto Day
15-Sep-08 Independence Day
2-Nov-08 All Souls Day
8-Dec-08 Immaculate Conception
25-Dec-08 Christmas
1-Jan-09 New Year's Day
1-Feb-09 Air Force Day
18-Feb-09 Ash Wednesday (start of Lent)
9-Apr-09 Holy Thursday
10-Apr-09 Holy Friday
12-Apr-09 Easter
1-May-09 Labor Day
27-May-09 Army Day
1-Aug-09 Fiesta Day
14-Sep-09 San Jacinto Day
15-Sep-09 Independence Day
2-Nov-09 All Souls Day
8-Dec-09 Immaculate Conception
25-Dec-09 Christmas
1-Jan-10 New Year's Day
1-Feb-10 Air Force Day
10-Feb-10 Ash Wednesday (start of Lent)
1-Apr-10 Holy Thursday
2-Apr-10 Holy Friday
4-Apr-10 Easter
1-May-10 Labor Day
27-May-10 Army Day
1-Aug-10 Fiesta Day
14-Sep-10 San Jacinto Day
15-Sep-10 Independence Day
2-Nov-10 All Souls Day
8-Dec-10 Immaculate Conception
25-Dec-10 Christmas
-------------------------------
Our guidelines for workscheduling code - just been drafted by my boss Today!
<for WorkDay scheduling of start date and finish date of our subcontractors>

1. if a holiday falls on friday, there is no work <with pay> on the
following day(sat).
2. if a holiday falls on Sunday, there is no work <with pay> on the
following day (mon).
3. there is a work on monday after easter sunday.
4. there is no work <with pay> if the day is between two holidays (e.g. see
31-Jan-2008,etc.)
5. there is no work <with pay> on monday if a holiday falls on tuesday.
(e.g. 31-Dec-2007, 26-May-2008, 07-Dec-2009, etc.)

i am really sorry for a late and confusing question. But this may be the
last shot. I may be drop-out of my workscheduling, very soon.

best wishes to all who help me
been dribled2

:

 

[Guest]

happy new year sir Bob Phillips,

considering the first scenario,
my boss tried the formula, and he ask me to hthwy.
He only give me two dates
date start = 2-jan-o7
date end = 4-apr-07 <@ 4months>
the manual count of workdays (inclusive of given dates) = 77 (same as your
formula's result)
yet
he gave again another headache date
date start = 2-jan-o7
date end = 8-apr-07 <sunday>
the manual count of workdays (inclusive of given dates) = 77
(but formula's result = 78)...i cant say anything to him....

after a debate again, i ask him to draft the guidelines...just today....
here is the correct list of holidays and in perfect series <increasing>
name as Holidays------------------------------
1-Jan-07 New Year's Day
1-Feb-07 Air Force Day
14-Feb-07 Ash Wednesday (start of Lent)
5-Apr-07 Holy Thursday
6-Apr-07 Holy Friday
8-Apr-07 Easter
1-May-07 Labor Day
27-May-07 Army Day
1-Aug-07 Fiesta Day
14-Sep-07 San Jacinto Day
15-Sep-07 Independence Day
2-Nov-07 All Souls Day
8-Dec-07 Immaculate Conception
25-Dec-07 Christmas
1-Jan-08 New Year's Day
30-Jan-08 Ash Wednesday (start of Lent)
1-Feb-08 Air Force Day
20-Mar-08 Holy Thursday
21-Mar-08 Holy Friday
23-Mar-08 Easter
1-May-08 Labor Day
27-May-08 Army Day
1-Aug-08 Fiesta Day
14-Sep-08 San Jacinto Day
15-Sep-08 Independence Day
2-Nov-08 All Souls Day
8-Dec-08 Immaculate Conception
25-Dec-08 Christmas
1-Jan-09 New Year's Day
1-Feb-09 Air Force Day
18-Feb-09 Ash Wednesday (start of Lent)
9-Apr-09 Holy Thursday
10-Apr-09 Holy Friday
12-Apr-09 Easter
1-May-09 Labor Day
27-May-09 Army Day
1-Aug-09 Fiesta Day
14-Sep-09 San Jacinto Day
15-Sep-09 Independence Day
2-Nov-09 All Souls Day
8-Dec-09 Immaculate Conception
25-Dec-09 Christmas
1-Jan-10 New Year's Day
1-Feb-10 Air Force Day
10-Feb-10 Ash Wednesday (start of Lent)
1-Apr-10 Holy Thursday
2-Apr-10 Holy Friday
4-Apr-10 Easter
1-May-10 Labor Day
27-May-10 Army Day
1-Aug-10 Fiesta Day
14-Sep-10 San Jacinto Day
15-Sep-10 Independence Day
2-Nov-10 All Souls Day
8-Dec-10 Immaculate Conception
25-Dec-10 Christmas
-------------------------------
Our guidelines for workscheduling code - just been drafted by my boss Today!
<for WorkDay scheduling of start date and finish date of our subcontractors>

1. if a holiday falls on friday, there is no work <with pay> on the
following day(sat).
2. if a holiday falls on Sunday, there is no work <with pay> on the
following day (mon).
3. there is a work on monday after easter sunday.
4. there is no work <with pay> if the day is between two holidays (e.g. see
31-Jan-2008,etc.)
5. there is no work <with pay> on monday if a holiday falls on tuesday.
(e.g. 31-Dec-2007, 26-May-2008, 07-Dec-2009, etc.)

i am really sorry for a late and confusing question. But this may be the
last shot. I may be drop-out of my workscheduling, very soon.

best wishes to all who help me
been dribled2

 

[Guest]

another typos excuse,

date start = 2-jan-o7
date end = 4-apr-07 <@ 4months>

the manual count of workdays (inclusive of given dates) = 78 (same as your
formula's result)

date start = 2-jan-o7
date end = 8-apr-07 <sunday>

the manual count of workdays (inclusive of given dates) = 78
(but formula's result = 77)

i am lost
been dribled2

 

[Guest]

another typos excuse,

date start = 2-jan-o7
date end = 4-apr-07 <@ 4months>

the manual count of workdays (inclusive of given dates) = 78 (same as your
formula's result)

date start = 2-jan-o7
date end = 8-apr-07 <sunday>

the manual count of workdays (inclusive of given dates) = 78
(but formula's result = 79)

i am lost
been dribled2

 

[Guest]

Bob,
Hope you're still interested in this one. You're definitely better with the
formula section of this than I am going to be for quite some time! See my
post and dribler2's response on down a bit. He relists the holidays and
gives new rules for determining which are paid holidays (and how to handle
them for a 6-day workweek). I'm kind of out of pocket this evening (New Yrs
Eve and have commitements to keep), so I cannot even begin to look at it
until tomorrow after a couple of aspirins <g>

 

[Guest]

Could you explain a little more about #4 - no work <with pay> (i.e., paid
holiday) if the day is between 2 holidays - ref 1/31/2008. I think I
understand - 1/30/08 is Ash Wednesday, 1/31/ is a 'regular' day, 2/1 is Air
Force Day, so because 1/31/ is between the two of them, it becomes a paid day
without work also.

How would that apply to the Holy Days from Holy Thursday thru Easter Sunday
- it appears to me as if that entire period from Holy Thursday through Easter
Sunday would be paid days off, since the Saturday is between Holy Friday and
Easter Sunday?

 

[Guest]

Dear Sirs,
thats right, since saturday is between rest day and holiday, there is a pay
and no work, during the holy day..
thanks and indeed happy new year 2007
been dribled 2007