Counting Mondays

Thanks:  0
Likes:  0

1. ## Counting Mondays

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!

2. ## Re: Counting Mondays

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

3. ## Re: Counting Mondays

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

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

4. ## Re: Counting Mondays

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!

5. ## Re: Counting Mondays

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

--Tom

6. ## Re: Counting Mondays

Hi Yevette:

another one I played with ...

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

7. ## Re: Counting Mondays

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

8. ## Re: Counting Mondays

Originally Posted by 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
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/200308/27/20032
2
09/07/200309/16/20032
 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•