Counting Mondays :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Counting Mondays
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Yevette
Board Master


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

Status: Offline

 Reply with quote  

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

Post Fri Sep 12, 2003 3:30 am 
 View user's profile Send private message

SydneyGeek
Board Master


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

Status: Offline

 Reply with quote  

Re: Counting Mondays

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

Post Fri Sep 12, 2003 4:04 am 
 View user's profile Send private message

Chitosunday
Board Master


Joined: 14 Jul 2003
Posts: 423

Flag: Philippines

Status: Offline

 Reply with quote  

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.

Post Fri Sep 12, 2003 4:21 am 
 View user's profile Send private message Send e-mail MSN Messenger

Yevette
Board Master


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

Status: Offline

 Reply with quote  

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

Post Fri Sep 12, 2003 4:06 pm 
 View user's profile Send private message

rrdonutz
Board Master


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

Status: Offline

 Reply with quote  

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

returns 35, instead of 31.

--Tom

Post Fri Sep 12, 2003 9:50 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


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

Status: Offline

 Reply with quote  

Re: Counting Mondays

Hi Yevette:

another one I played with ...

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

Post Fri Sep 12, 2003 10:07 pm 
 View user's profile Send private message

rrdonutz
Board Master


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

Status: Offline

 Reply with quote  

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

Post Sat Sep 13, 2003 10:48 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


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

Status: Offline

 Reply with quote  

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
=

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

Post Sat Sep 13, 2003 11:18 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


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

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.