Count Mondays between 2 given dates

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
Hi all

I have a month and year entered in A2 in the format "mmm-yy". In B2 i need a formula which counts the number of mondays in that month.


Thanks

P.s apologies for putting a slightly different title to my question, i realised half way through typing that id only ever want to count mondays in whole months. So i changed the question but forgot to change the title. Doh!
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Soggy

Board Regular
Joined
May 16, 2012
Messages
188
You may have a problem here as although the format is displaying "mmm-yy" the cell is holding a value that will translate as a date, so usually MAR-12 will be 01/03/12 if you only typed in the month/year value.

So a formula will not be able to recognise a range to calculate on. There could be another way but you may have to list out all the days then use "weekday" function to count occurences of days.
 

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
What if i used month start (a2) and month end (b2)? would that be enough info to calculate mondays?
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Hi

Assuming the value in A1 is an actual date and is the first of the month, then you can use the following formula:

=INT((32-DAY(A1+32)+WEEKDAY(A1-2))/7)
 

Soggy

Board Regular
Joined
May 16, 2012
Messages
188
Sorry I must apologies I was thinking of this in the wrong way.

Try this in cell B2 where the date is in cell A2 (should work for any date input)

=ROUNDDOWN(((EOMONTH(A2,0)-DATE(YEAR(A2),MONTH(A2),1))+WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2)-1)/7,0)+IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2)=1,1,0)

This should work out what the first day of the month is and calculate from there. I ahve teted over a rnage of about a year and it seems to work but you may want to test yourself to make sure.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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
Top