Count Mondays between 2 given dates

[CROY1985]

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!

 

[Soggy]

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]

What if i used month start (a2) and month end (b2)? would that be enough info to calculate mondays?

 

[Firefly2012]

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]

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]

Firefly2012's sollution works. Thanks for the help guys.