# Count Mondays between 2 given dates

#### CROY1985

##### Active Member
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
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
What if i used month start (a2) and month end (b2)? would that be enough info to calculate mondays?

#### Firefly2012

##### Well-known Member
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
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.

#### CROY1985

##### Active Member
Firefly2012's sollution works. Thanks for the help guys.

Replies
15
Views
392
Replies
1
Views
386
Replies
0
Views
114
Replies
9
Views
226
Replies
14
Views
731

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.

### Which adblocker are you using?

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

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