return work week number for a date

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 24, 2008

• #2

=WEEKNUM(TODAY())

Requires the Analysis Toolpack Addin from Tools - Addins

 

Upvote 0

M

mcgew

Board Regular

Joined

Sep 4, 2002

Messages

75

• Jan 24, 2008

• #3

that's brilliant, thanks for much for your quick response

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jan 24, 2008

• #4

Without the ATP and depending on exactly waht you want, this formula gives the equivalent of WEEKNUM(A1,2):

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

 

Upvote 0

N

ntufwc5

New Member

Joined

Mar 28, 2008

Messages

14

• Apr 28, 2008

• #5

RichardSchollar said:

Without the ATP and depending on exactly waht you want, this formula gives the equivalent of WEEKNUM(A1,2):

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

Click to expand...

Hi Richard,

Is there any way to put this in a VBA ? Let say , I have a column A filled with dates and would like to return corresponding weeknumber in respective cell in column B. Please advise.

 

Upvote 0

A

ac3100

Board Regular

Joined

Aug 21, 2002

Messages

185

• Apr 29, 2008

• #6

Thanks for this.

Andy

 

Upvote 0