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 ...

******** ******************** ************************************************************************>

Microsoft Excel - y030912h1.xls ___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C1C2 =
	A	B	C	D
1	08/16/2003	08/27/2003	2	*
2	09/07/2003	09/16/2003	2	*
Sheet2a *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.