Whats the Week of the Month?

[ceemo]

Is there a function or code for a function that will give me the week of the month?

[Bob Phillips]

What's the criteria, does it start on 1st, first Monday, etc.?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> Is there a function or code for a function that will give me the week of
> the month?
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=567094
>

[Toppers]

Assuming week 1 starts on the 1st of a month:

=INT((DAY(A1)/7)+1)

where A1 is the date

Is this what you want?

"ceemo" wrote:

>
> Is there a function or code for a function that will give me the week of
> the month?
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=567094
>
>

[David Biddulph]

"Toppers" <[email protected]> wrote in message
news:[email protected]...

> "ceemo" wrote:
>> Is there a function or code for a function that will give me the week of
>> the month?

> Assuming week 1 starts on the 1st of a month:
>
> =INT((DAY(A1)/7)+1)
>
> where A1 is the date
>
> Is this what you want?

Wouldn't that always make week 1 a 6 day week?
--
David Biddulph

[ceemo]

well the original requirement was that if the 1st was between a thursday and a sunday inclusive then that should be week four and the following week, week 1. If the first was between a monday and wednesay day then that would count as week one.

This was how it was explained to me but im thinking this ma be to much effort

[Toppers]

You're right .... should be

=INT((DAY(A1)-1)/7)+1

Thanks

"David Biddulph" wrote:

> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
>
> > "ceemo" wrote:
> >> Is there a function or code for a function that will give me the week of
> >> the month?
>
> > Assuming week 1 starts on the 1st of a month:
> >
> > =INT((DAY(A1)/7)+1)
> >
> > where A1 is the date
> >
> > Is this what you want?
>
> Wouldn't that always make week 1 a 6 day week?
> --
> David Biddulph
>
>
>

[Bob Phillips]

=INT((M1-(DATE(YEAR(M1),MONTH(M1),1)-WEEKDAY(DATE(YEAR(M1),MONTH(M1),1),2)+1
))/7)+1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> well the original requirement was that if the 1st was between a thursday
> and a sunday inclusive then that should be week four and the following
> week, week 1. If the first was between a monday and wednesay day then
> that would count as week one.
>
> This was how it was explained to me but im thinking this ma be to much
> effort
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=567094
>