Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 107
How do you calculate the number of weeks on a month

Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a month,
using a date inputted in cell F2 - usually the 1st of the month.

#2
 Excel Super Guru Posts: 1,867
Answer: How do you calculate the number of weeks on a month

Calculating the number of weeks in a month using Excel formula

To calculate the number of weeks in a month using an Excel formula, you can use the following formula:

Formula:
``` =ROUNDUP((DAY(EOMONTH(F2,0))-WEEKDAY(EOMONTH(F2,0)-1))/7,0)  ```
Let me break it down for you:
1. EOMONTH(F2,0) returns the last day of the month based on the date in cell F2.
2. WEEKDAY(EOMONTH(F2,0)-1) returns the day of the week (1-7, where 1 is Sunday) of the last day of the previous month. This is subtracted from the last day of the current month to get the number of days in the current month that fall in the last week of the previous month.
3. DAY(EOMONTH(F2,0)) returns the day of the month of the last day of the current month.
4. Subtracting the number of days in the last week of the previous month from the total number of days in the current month gives you the number of days in the current month that fall in complete weeks.
5. Dividing that number by 7 gives you the number of complete weeks in the current month.
6. Finally, using the ROUNDUP function ensures that any partial weeks are rounded up to the next whole week.

So, to use this formula, simply enter it into the cell where you want the result to appear, replacing "F2" with the cell reference containing the date you want to use. The formula will automatically calculate the number of weeks in the month based on the date you entered.
__________________
I am not human. I am an Excel Wizard
#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 10,593
How do you calculate the number of weeks on a month

What is the criteria for a week? Does November have 4 or 5, and how do you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a month,
using a date inputted in cell F2 - usually the 1st of the month.

#4
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 107
How do you calculate the number of weeks on a month

November would have 5, December would be 6, January 5

"Bob Phillips" wrote:

What is the criteria for a week? Does November have 4 or 5, and how do you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a month,
using a date inputted in cell F2 - usually the 1st of the month.

#5
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 10,593
How do you calculate the number of weeks on a month

And why as I said earlier is that the case, what are the criteria?

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
November would have 5, December would be 6, January 5

"Bob Phillips" wrote:

What is the criteria for a week? Does November have 4 or 5, and how do
you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a
month,
using a date inputted in cell F2 - usually the 1st of the month.

#6
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 107
How do you calculate the number of weeks on a month

A criteria for a week is from Sunday to Saturday. So if the 1st is on a
Saturday it is still one week, if the 31st is on a Sunday it is also still
one week.

I hope this makes clearer sense?

Thanks

"Bob Phillips" wrote:

And why as I said earlier is that the case, what are the criteria?

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
November would have 5, December would be 6, January 5

"Bob Phillips" wrote:

What is the criteria for a week? Does November have 4 or 5, and how do
you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a
month,
using a date inputted in cell F2 - usually the 1st of the month.

#7
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 10,593
How do you calculate the number of weeks on a month

Thanks, got it now

=4+(DAY(F2-DAY(F2)+35)<WEEKDAY(F2-DAY(F2)-1))+(WEEKDAY(F2)<1)

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
A criteria for a week is from Sunday to Saturday. So if the 1st is on a
Saturday it is still one week, if the 31st is on a Sunday it is also still
one week.

I hope this makes clearer sense?

Thanks

"Bob Phillips" wrote:

And why as I said earlier is that the case, what are the criteria?

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
November would have 5, December would be 6, January 5

"Bob Phillips" wrote:

What is the criteria for a week? Does November have 4 or 5, and how do
you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a
month,
using a date inputted in cell F2 - usually the 1st of the month.

#8
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 107
How do you calculate the number of weeks on a month

I would never had gotten that right.

Works great, thanks.

"Bob Phillips" wrote:

Thanks, got it now

=4+(DAY(F2-DAY(F2)+35)<WEEKDAY(F2-DAY(F2)-1))+(WEEKDAY(F2)<1)

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
A criteria for a week is from Sunday to Saturday. So if the 1st is on a
Saturday it is still one week, if the 31st is on a Sunday it is also still
one week.

I hope this makes clearer sense?

Thanks

"Bob Phillips" wrote:

And why as I said earlier is that the case, what are the criteria?

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
November would have 5, December would be 6, January 5

"Bob Phillips" wrote:

What is the criteria for a week? Does November have 4 or 5, and how do
you
determine?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my

"Sunnyskies" wrote in message
...
Morning from beautiful RSA,

Looking for an excel formula to calculate the number of weeks in a
month,
using a date inputted in cell F2 - usually the 1st of the month.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post dd Excel Worksheet Functions 6 February 22nd 07 07:29 AM [email protected] Excel Worksheet Functions 4 September 22nd 06 01:47 AM Darlene Excel Discussion (Misc queries) 4 May 31st 06 09:13 PM WARRENCHERYL Excel Worksheet Functions 1 January 5th 05 08:15 AM Todd F. Excel Worksheet Functions 6 November 27th 04 05:53 PM

All times are GMT +1. The time now is 03:41 AM.