Counting Mondays

[Yevette]

Hello All,

I know there are several postings out there on this subject, but I can't seem to make the formula work.

I have a start date of 9/15/03 (A2) and an end date of 10/10/03 (B2). I need a formula that will calculate the total number of Mondays in this period (not "between" the start and end dates of this period). Can someone help me with a formula to resolve this? The formula I'm using now is:

=INT((($B$2-$A$2)-MOD(2-$B$2,7))/7)

but it's coming up one Monday short. Can someone help me? Thanks a lot!

 

[SydneyGeek]

INT usually rounds down. Why not just add 1 to the result?

 

[Chitosunday]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=2))

or another formula
=INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>=2,1,0)

 

[Yevette]

Hi Chito!

Worked like a charm! I thought I already tried this formula from an earlier post (I think it was your post actually), but couldn't get the formula to work. I probably transposed a cell address or something. But thanks again. Have a great day!

 

[rrdonutz]

Another non-volatile possibility:

=INT((B2-2)/7)-INT((A2-2)/7)+INT(WEEKDAY(A2-2)/7)

The following is not reliable in its current form:

=INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>=2,1,0)

For example, if A2 = 8/01/03 and B2 = 8/31/03, then it returns 5, instead of 4, and

=SUMPRODUCT(INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>={1;2;3;4;5;6;7},1,0))

returns 35, instead of 31.

--Tom

 

[Yogi Anand]

Hi Yevette:

another one I played with ...

=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)

 

[rrdonutz]

I don't like being a naysayer, but:

=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)

is not reliable either. For examples, try 8/16/03 & 8/27/03, and 9/7/03 & 9/16/03 in cells A1 and B1, respectively.

--Tom

 

[Yogi Anand]

I don't like being a naysayer, but:

=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)

is not reliable either. For examples, try 8/16/03 & 8/27/03, and 9/7/03 & 9/16/03 in cells A1 and B1, respectively.
--Tom

Hi Tom:

Thanks for pointing that out. I have corrected the formula now to read ...

=INT((B1-A1)/7)+OR(WEEKDAY(A1)<=2,WEEKDAY(B1)>=2)

as illustrated in ...

y030912h1.xls
	A	B	C	D
1	08/16/2003	08/27/2003	2
2	09/07/2003	09/16/2003	2
Sheet2a