Counting Mondays

Yevette

Yevette

Active Member
Joined
Mar 8, 2003
Messages
336
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=2))

or another formula
=INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>=2,1,0)
 
Upvote 0
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!
 
Upvote 0
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)
Click to expand...
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
 
Upvote 0
I don't like being a naysayer, but:
=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
Click to expand...
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
 
Upvote 0
rrdonutz said:
I don't like being a naysayer, but:
=INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
Click to expand...
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
Click to expand...
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
ABCD
108/16/200308/27/20032
209/07/200309/16/20032
Sheet2a
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
492
felixstraube
F
A
  • Question Question
Filter between 2 dates
Replies
5
Views
72
Joe4
Joe4
J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
768
felixstraube
F
itsgrady
  • Solved
Weekdays only for 1 Month
Replies
7
Views
381
Fluff
Fluff
A
  • Locked
  • Question Question
Formula for dates that fall in a certain period ( between two dates)
Replies
1
Views
46
Joe4
Joe4

Forum statistics

Threads
1,225,138
Messages
6,183,087
Members
453,146
Latest member
Lacey D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top