How many weeks in a month?

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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>
 
Upvote 0
Sorry Barry I did not see your post. How would I modify that to Friday?
 
Upvote 0
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
 
Upvote 0
Thank you Teethless mama and barry houdini. I have a question for Barry. Could you please explain the +34 in you formula?

Thanks Stephen
 
Upvote 0
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
 
Upvote 0
Thanks Barry that is a great explanation. It is much appreciated.

Thanks Stephen
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top