A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do you calculate the number of weeks on a month



 
 
Thread Tools Display Modes
  #1  
Old November 22nd 07, 09:05 AM posted to microsoft.public.excel.misc
Sunnyskies
external usenet poster
 
Posts: 107
Default 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.

Thanks in advance.
Ads
  #2  
Old November 22nd 07, 09:17 AM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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.
>
> Thanks in advance.



  #3  
Old November 22nd 07, 09:50 AM posted to microsoft.public.excel.misc
Sunnyskies
external usenet poster
 
Posts: 107
Default 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.
> >
> > Thanks in advance.

>
>
>

  #4  
Old November 22nd 07, 10:10 AM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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
>> 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.
>> >
>> > Thanks in advance.

>>
>>
>>



  #5  
Old November 22nd 07, 10:31 AM posted to microsoft.public.excel.misc
Sunnyskies
external usenet poster
 
Posts: 107
Default 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
> >> 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.
> >> >
> >> > Thanks in advance.
> >>
> >>
> >>

>
>
>

  #6  
Old November 22nd 07, 10:42 AM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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
>> 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
>> >> 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.
>> >> >
>> >> > Thanks in advance.
>> >>
>> >>
>> >>

>>
>>
>>



  #7  
Old November 22nd 07, 10:54 AM posted to microsoft.public.excel.misc
Sunnyskies
external usenet poster
 
Posts: 107
Default 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
> >> 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
> >> >> 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.
> >> >> >
> >> >> > Thanks in advance.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count the number of even weeks in the current month. dd Excel Worksheet Functions 6 February 22nd 07 07:29 AM
Calculate Number of Months Weeks and Days Between Two Dates [email protected] Excel Worksheet Functions 4 September 22nd 06 01:47 AM
Calculate number of weeks between dates in Excel 2000 Darlene Excel Discussion (Misc queries) 4 May 31st 06 09:13 PM
HOW TO CALCULATE NUMBER OF WEEKS BETWEEN TWO GIVEN DATES(MAY BE . WARRENCHERYL Excel Worksheet Functions 1 January 5th 05 08:15 AM
calculate weeks from a start date ( not yr weeks) Todd F. Excel Worksheet Functions 6 November 27th 04 05:53 PM


All times are GMT +1. The time now is 06:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.