Formula for Week# of Month

iliace

Well-known Member

Joined

Jan 15, 2008

Messages

3,562

Office Version

1. 365

Platform

1. Windows

• Mar 12, 2008

• #2

I can't think of a built-in, or otherwise efficient way to get that result, but try this formula:

=SUMPRODUCT(--(DATE(YEAR(B12),MONTH(B12),ROW($1:$31))<=B12),--(WEEKDAY(DATE(YEAR(B12),MONTH(B12),ROW($1:$31)),1)=1))+1

Where B12 is where you have the date you want the week number for.

 

Last edited: Mar 12, 2008

Upvote 0

arthurbr

Well-known Member

Joined

Dec 8, 2006

Messages

2,791

Office Version

1. 365

Platform

1. Windows

• Mar 12, 2008

• #3

Hi Kuih,
how do you define the first week of a month?
Is it the week containing the 1st day of the month or the first full week of the month?

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Mar 12, 2008

• #4

If it works like weeknum then the first of every month will be week 1 and week 2 will start on the next Sunday. Of course this means that some months will go up to week 6. To do that, with date in A1

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

 

Upvote 0

K

Kuih

New Member

Joined

May 24, 2005

Messages

29

• Mar 12, 2008

• #5

Dear Arthur,

Thanks for your reply, the first day of the week is the week containing the 1st day of the month. As you know, WEEKNUM does take that into consideration and calculates it beautifully and you can specify the first day of the week can either be a Sunday (return value 1) or a Monday (2).

Dear barry and iliace,

Thanks for your solution, I will test your code right away.

However, the question is simple - rather than those lengthy codes, does anyone know a built-in formula that calculates this? If not, I wonder why Microsoft didn't include it in the first place...

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Mar 12, 2008

• #6

Hello Kuih,

No, there's no built in formula for this, I don't think it's that common to start from week 1 every month.

Try my suggestion, it isn't too complicated

 

Upvote 0

iliace

Well-known Member

Joined

Jan 15, 2008

Messages

3,562

Office Version

1. 365

Platform

1. Windows

• Mar 12, 2008

• #7

In my solution, to change the determining weekday from Sunday to any other weekday, simply change the portion highlighted in red below:

=SUMPRODUCT(--(DATE(YEAR(B12),MONTH(B12),ROW($1:$31))<=B12),--(WEEKDAY(DATE(YEAR(B12),MONTH(B12),ROW($1:$31)),1)=1))+1

=1 is sunday
=2 is monday
=3 is tuesday
etc...
=7 is saturday

I haven't examined Houdini's solution, but since his uses WEEKDAY() function also, I'm guessing you can do the same thing.

 

Upvote 0

K

Kuih

New Member

Joined

May 24, 2005

Messages

29

• Mar 12, 2008

• #8

Dear Barry and Iliace,

Thank you for your timely reply on this matter. Anyway, regarding your code;

Given by an example: March 2008 is a tricky one.

March 1st is a Saturday (not a working day)
March 3rd is a Monday (a working day and should be Week 1)

 

Upvote 0

iliace

Well-known Member

Joined

Jan 15, 2008

Messages

3,562

Office Version

1. 365

Platform

1. Windows

• Mar 13, 2008

• #9

That's not the functionality of WEEKNUM. Try WEEKNUM with Jan 1, 2011 and Jan 3, 2011 (identical to your example).

With the functionality you're requesting, what should the output be for March 1, 2008? Last week of February 2008? Week zero?

 

Upvote 0

L

lalbatros

Well-known Member

Joined

Sep 5, 2007

Messages

659

• Mar 13, 2008

• #10

If you want to mimick the ISO definition of a week number, but for the month scale,
you might use the VBA code from Pearson:

http://www.cpearson.com/excel/DateTimeVBA.htm

and adjust it to do the same on a month scale.

 

Upvote 0