# of week in a month

[Rick]

Anyone have a formula or method for determining which week
# in a month a day is. The cell I am referencing will
have a date in MM/DD/YYYY format.

I can already get if the day is a monday, tuesday, etc.
But I am trying to get to if it's the first, second,
third, etc Monday in the month.

Thanks in advance,

Rick

 

[Daniel.M]

Hi Rick,,

It depends how you define your 'weeks' numbering scheme.
Take a look here : http://www.cpearson.com/excel/weeknum.htm

Does it follow a given norm (ISO or American) starting from the beginning of the
year or do you see it in 'absolute' terms (March 1 starts the 1st week of that
month).

In the latter case, with a date in A1:
=MATCH(DAY(A1),{1;8;15;22;29})

Otherwise, precise your preferred system, give a few examples illustrating your
numbering scheme, and we'll provide more appropriate responses.

Regards,

Daniel M.

 

[Harlan Grove]

Anyone have a formula or method for determining which week
# in a month a day is. The cell I am referencing will
have a date in MM/DD/YYYY format.

I can already get if the day is a monday, tuesday, etc.
But I am trying to get to if it's the first, second,
third, etc Monday in the month.

Ambiguous. The following formula returns the number of Mondays in the month on
or before the date SomeDate.

=MATCH(SomeDate,SomeDate-DAY(SomeDate)-WEEKDAY(SomeDate-DAY(SomeDate),3)
+{0;7;14;21;28;35})-1

 

[Guest]

Hi Daniel:

I/m not sure I am really follwing either of the schemes.

It's really based on occurrence of that day in the month.

For example, using the current month Jan 04.

When I see the date 01/01/04 I would want in a cell that
this is a Thursday (which I have already accomplished with
the weekday function).

However, I also want another cell that would calculate to
1 because this is the first Thursday in January 04.

for 01/08/04 the function would return 2 as this is the
second Thursday in January.

Hope this explains more clearly.

thanks,

Rick

 

[Daniel.M]

Hi,

It's really based on occurrence of that day in the month.
...
for 01/08/04 the function would return 2 as this is the
second Thursday in January.

Then the proposed formula will work.

Regards,

Daniel M.

 

[Paul]

Anyone have a formula or method for determining which week
# in a month a day is. The cell I am referencing will
have a date in MM/DD/YYYY format.

I can already get if the day is a monday, tuesday, etc.
But I am trying to get to if it's the first, second,
third, etc Monday in the month.

Thanks in advance,

Rick

An alternative to the formula already given:
=ROUNDUP(DAY(A1)/7,0)

 

[Ron Rosenfeld]

Anyone have a formula or method for determining which week
# in a month a day is. The cell I am referencing will
have a date in MM/DD/YYYY format.

I can already get if the day is a monday, tuesday, etc.
But I am trying to get to if it's the first, second,
third, etc Monday in the month.

Thanks in advance,

Rick

Wouldn't it be as simple as:

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

or
=ROUNDUP(DAY(A1)/7,0)


--ron

 

[Daniel.M]

Hi Ron,

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

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

The 7th day of the month should return 1.

Regards,

Daniel M.

 

[Ron Rosenfeld]

Hi Ron,


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

The 7th day of the month should return 1.

Regards,

Daniel M.

Thanks. Well, I also posted the ROUNDUP function, which should work OK.


--ron