count how many weekends are in a month

[Davers]

Is there a formula that will return the number of weekends between 2 dates?

Thanks for any help and have a good day,

Dave

 

[rrdonutz]

Hi Dave,

This formula will return the number of Saturdays and Sundays between 2 dates:

=SUM(INT((B1-{1,7})/7)-INT((A1-{1,7}-1)/7))

where A1 is the beginning date and B1 is the ending date, inclusive. So:

=SUM(INT((B1-{1,7})/7)-INT((A1-{1,7}-1)/7))/2

would return the number of weekends, in a rough sense, since it has the possibility of returning a fractional amount. Of course, the INT() function would solve that, but the question then becomes how you want to handle a situation where A1 equals 25 April 2004 and B1 equals 1 May 2004. Obviously, there is a Saturday/Sunday pair here, but not together. The above equation needs to change to handle the situation where you want to count only whole bundled weekends.

--Tom

 

[BrianB]

This might ot be exactly what you want because it depends how you want to count a month beginning with Sunday or ending on Saturday (eg. July 2004). This could be adapted :-

=INT((B1-A1)/7)+IF(WEEKDAY(A1,2)>5,1,0)+IF(WEEKDAY(B1,2)>5,1,0)

 

[Davers]

Thanks Tom!!! Worked perfectly! I don't care when the weekends are together or not....your formula worked great!!!!

Thanks again and have a good day,

Dave ray:

 

[rrdonutz]

Here is a follow-on to my post above:

MrE4264.xls
	A	B	C	D
1	Begdate	Enddate	Sat/SunPairs	Sat/Sunbundles
2	1-Apr-04	30-Apr-04	4	4
3	1-May-04	29-May-04	4.5	4
4	2-May-04	31-May-04	4.5	4
5	1-Feb-04	28-Feb-04	4	3
6	1-Aug-04	28-Aug-04	4	3
7	31-Jul-04	29-Aug-04	5	5
8	1-Nov-04	29-Nov-04	4	4
Sheet3

The formula in C2 is:

=SUM(INT((B2-{1,7})/7)-INT((A2-{1,7}-1)/7))/2

while that in D2 is:

=INT((SUM(INT((B2-{1,7})/7)-INT((A2-{1,7}-1)/7))-(WEEKDAY(A2)=1))/2)

--Tom

(I don't believe BrianB's formula returns the same results.)

 

[rrdonutz]

You're welcome, Dave. Glad to help.

 

[Davers]

Brian, thanks for responding...I'll pop your formula in and see how it works. Currently, I'm not sure how we are going to handle all the weekend situations. I was using NETWORKDAYS with a range of holidays to calculate how long it took to complete a project...However, they now want to know how many projects were started within 48 hours....I have 27 departments and countless projects to calculate. I can't think of any way to exclude weekends and holidays when I do a straight date - date calculation, but I thought this would be a step in the right direction. In fact, if everyone would take a look at this example and maybe suggest how to exclude weekends and holidays from my calculation...remember, the total time must be in [hh]:mm:ss because they want to know how many were started within 48 hours, not 2 business days like I had been doing...

Book1
	A	B	C	D	E	F
1	SRNumber	Created	InProgressDate	TotalHours	Hours-Weekend	BusinessDays:
2	1-7H3HKT	3/12/200413:22	3/16/200412:53	95:31:19	47:31:19	3
Sheet1

Thanks for any help!!!

Have a good day,

Dave

 

[rrdonutz]

Dave, I'm not quite sure where you want to go with this, but maybe the following is of some use:

MrE4264.xls
	A	B	C	D	E	F
1	SRNumber	CreatedIn	ProgressDate	TotalHours	Wkends&Hols	Hrs-(Wkends&Hols)
2	1-7H3HKT	3/12/0413:22	3/16/0412:53	95:31:19	2	47:31:19
3	2-7J4JLU	4/1/0413:22	4/16/0412:50	359:28:00	5	239:28:00
4	3-7K5KMV	3/12/0413:22	4/2/0412:50	503:28:00	6	359:28:00
5	3-8K5LNW	4/1/0413:22	4/2/0411:08	21:46:00	0	21:46:00
6	3-8K5LNY	4/30/0413:00	5/4/0413:45	96:45:00	2	48:45:00
7	3-8K5LPP	8/30/049:25	9/7/0410:40	193:15:00	3	121:15:00
8	3-9H2FCF	11/24/0415:00	11/29/0412:30	117:30:00	4	21:30:00
9
10	Holidays
11	1/1/04
12	4/9/04
13	5/31/04
14	7/5/04
15	9/6/04
16	11/25/04
17	11/26/04
18	12/24/04
Sheet4

Formulas are--

D2: =C2-B2
E2: =INT(C2)-INT(B2)-NETWORKDAYS(B2,C2,Holidays)+1
F2: =D2-E2

Note: Some mods to the above are necessary if you allow start or progress date/times to fall on a weekend or holiday.

HTH,

Tom

 

[Davers]

Tom, very nice, that will come in handy!!! I appreciate you taking the time to help! I was at a stand still and getting ready to tell them they couldn't have that...

Thanks again and have a good day,

Dave