date to week number of the year

[jamielill]

I want to find the week of the year were this lands

If i have a date that could be

june/31/2012 or

jun/1/2012

would it be best to extract the text from the left end of the date

match it to a month 1 to 12

extract the 6 & 7th character from the right of the date
match it to the day of the week

the use Chips formula for week of the year.

or am I over complicating things to much

Is there an excel command that does this in 2003

 

[Jonmo1]

There is a function called Weeknum

Assuming the date in A1 is a real date, not just a text string that looks like a date.
=WEEKNUM(A1)

This does require the analysis toolpack from Tools - Addins.

 

[jamielill]

thanks it is a text string though

 

[Jonmo1]

Try

=WEEKNUM(REPLACE(A1,1,FIND("/",A1)-1,MONTH(LEFT(A1,FIND("/",A1)-1)&1)))

 

[Rick Rothstein]

Try

=WEEKNUM(REPLACE(A1,1,FIND("/",A1)-1,MONTH(LEFT(A1,FIND("/",A1)-1)&1)))

Or this way...

=WEEKNUM(1*SUBSTITUTE(SUBSTITUTE(A1,"/"," ",1),"/",", "))

 

[Jonmo1]

Or this way...

=WEEKNUM(1*SUBSTITUTE(SUBSTITUTE(A1,"/"," ",1),"/",", "))

Nice..

FYI, you don't need to coerce the substitute to number with 1*
Weeknum does it for you.

 

[Rick Rothstein]

FYI, you don't need to coerce the substitute to number with 1*
Weeknum does it for you.

Thanks for noting that. I needed the coercion in my test to force the date, then I simply wrapped it with the WEEKNUM function... I didn't even think to try removing the coercion after doing that.

 

[jamielill]

Thanks guys
I was going to us vlookup & mid and replace the text with a number then lookup the number of the week