How many weeks in a month?

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
986
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Thanks in advance Stephen
Book1
ABCD
1MonthYearWeeks
2April20084
Sheet1
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Mar 17, 2003
Messages
986
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I guess first to last friday in the month.
 

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
986
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Mar 31, 2007
Messages
205

ADVERTISEMENT

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
Joined
Mar 17, 2003
Messages
986
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Mar 17, 2003
Messages
986
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks Barry that is a great explanation. It is much appreciated.

Thanks Stephen
 

Watch MrExcel Video

Forum statistics

Threads
1,122,905
Messages
5,598,778
Members
414,259
Latest member
beetle12

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