Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
Why 597?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "dribler2" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "Bob Phillips" wrote in message ... 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) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
Excluding one each of the duplicates...
Means removing those entries from the holiday list. Biff "T. Valko" wrote in message ... 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 "Bob Phillips" wrote in message ... 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) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "T. Valko" wrote: |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "Bob Phillips" wrote: Why 597? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "dribler2" wrote: 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 "Bob Phillips" wrote: Why 597? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "Bob Phillips" wrote: Why 597? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "dribler2" wrote in message ... 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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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? "dribler2" wrote: 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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
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 "JLatham" wrote: 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? "dribler2" wrote: 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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
i am lost
You're not the only one! With all these new rules it makes this extremely complicated. Biff "dribler2" wrote in message ... 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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count easily
thanks and Happy New Year
more power romelsb "T. Valko" wrote: i am lost You're not the only one! With all these new rules it makes this extremely complicated. Biff "dribler2" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |