Counting Mondays :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Counting Mondays

Yevette
Board Master

Joined: 09 Mar 2003
Posts: 238
Location: Burbank, CA
Flag:

Status: Offline

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!
_________________
"To err is human--and to blame it on a computer is even more so.”

Fri Sep 12, 2003 3:30 am

SydneyGeek
Board Master

Joined: 06 Aug 2003
Posts: 596
Location: Sydney, Australia
Flag:

Status: Offline

Re: Counting Mondays

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

Fri Sep 12, 2003 4:04 am

Chitosunday
Board Master

Joined: 14 Jul 2003
Posts: 423

Flag:

Status: Offline

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)
_________________
Mr Young at Heart - Learning is like chasing the wind, it's boundless.

Fri Sep 12, 2003 4:21 am

Yevette
Board Master

Joined: 09 Mar 2003
Posts: 238
Location: Burbank, CA
Flag:

Status: Offline

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!
_________________
"To err is human--and to blame it on a computer is even more so.”

Fri Sep 12, 2003 4:06 pm

rrdonutz
Board Master

Joined: 16 Jan 2003
Posts: 437
Location: Round Rock, Texas
Flag:

Status: Offline

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:

quote:

=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

Fri Sep 12, 2003 9:50 pm

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

Re: Counting Mondays

Hi Yevette:

another one I played with ...

=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
_________________
Regards!
Yogi Anand

Fri Sep 12, 2003 10:07 pm

rrdonutz
Board Master

Joined: 16 Jan 2003
Posts: 437
Location: Round Rock, Texas
Flag:

Status: Offline

Re: Counting Mondays

I don't like being a naysayer, but:

quote:
=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

Sat Sep 13, 2003 10:48 pm

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

Re: Counting Mondays

quote:
Originally posted by rrdonutz:
I don't like being a naysayer, but:
quote:
=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.

_________________
Regards!
Yogi Anand

Sat Sep 13, 2003 11:18 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum