# How many weeks in a month?

#### Stephen_IV

##### Well-known Member
Good afternoon. I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.

Book1
ABCD
1MonthYearWeeks
2April20084
Sheet1

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Andrew Fergus

##### MrExcel MVP
Hi Stephen

What criteria do you want to decide how many weeks are in the month? Without any criteria every month would be 4 weeks and yet we know intuitively that would be wrong. Is there a particular day in the month that you want to count?

Andrew

#### barry houdini

##### MrExcel MVP
Assuming each week begins on a Monday then you probably need a count of Mondays within the month, you could do that with this formula in D2

=4+(DAY((1&A2&B2)+34)< WEEKDAY(6&A2&B2))<WEEKDAY(6&AMP;A2&AMP;B2))< p>

#### Stephen_IV

##### Well-known Member
I guess first to last friday in the month.

#### Stephen_IV

##### Well-known Member

Sorry Barry I did not see your post. How would I modify that to Friday?

#### barry houdini

##### MrExcel MVP
The 6 in the formula determines the day of the week, Sundays will be 7, Mondays 6, Tuesdays 5 etc. so to count Fridays in the month change the 6 to a 2

#### Teethless mama

##### Board Regular

Try this:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(B2,MONTH(A2&1),1)&":"&EOMONTH(DATE(B2,MONTH(A2&1),1),0))))=6))

#### Stephen_IV

##### Well-known Member
Thank you Teethless mama and barry houdini. I have a question for Barry. Could you please explain the +34 in you formula?

Thanks Stephen

#### barry houdini

##### MrExcel MVP
Could you please explain the +34 in you formula?

Every month has a minimum of 4 Fridays so my suggested formula is in the form

=4+(Test)

The test returns either TRUE or FALSE and the formula evaluates to 5 or 4 accordingly.....we just need a test that will only be TRUE when there are 5 Fridays in a month.....

Whether that happens or not is determined by a combination of the number of days in the month and the start day of the month, i.e. if a month has 31 days then for it to contain 5 Fridays the month must start on a Wed, Thu or Fri.

Similarly if a month has 30 days then to have 5 Fridays it must begin on a Thu or Fri....a 29 day month must begin on a Friday...a 28 day month can't have 5 Fridays.

So we can use the weekday function to check this. If we check the WEEKDAY of the 2nd of the month, i.e. WEEKDAY(2&A2&B2) then when month has 31 days then WEEKDAY(2&A2&B2) must be 5, 6 or 7 for month to have 5 Fridays.

Similarly if month has 30 days then WEEKDAY(2&A2&B2) must be 6 or 7....and if month has 29 days WEEKDAY(2&A2&B2) must be 7.

...so we need a function that returns a 4 when month has 31 days, 5 when it has 30, 6 when it has 29 and 7 when it has 28.

If we have such a function we can use

=4+(function< WEEKDAY(2&A2&B2))

so that's where

=DAY((1&A2&B2)+34) comes in.

1&A2&B2 gives the 1st of the month, when we add 34 we get a date in the following month, if current month has 31 days then that will be 4th of the month, if current month has 30 days that'll give the 5th, 29 days gives the 6th and 28 days the 7th......so DAY just extracts the number, 4, 5, 6 or 7 and we have the finished formula

=4+(DAY((1&A2&B2)+34)< WEEKDAY(2&A2&B2))

The first part remains constant but the the 2nd part needs to be varied depending on the day of the week to count, as described in my previous post

#### Stephen_IV

##### Well-known Member
Thanks Barry that is a great explanation. It is much appreciated.

Thanks Stephen

Replies
4
Views
196
Replies
6
Views
47
Replies
1
Views
39
Replies
2
Views
235
Replies
0
Views
135

1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

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