Convert a general format string to calendar date in Excel 2010 vba?

I have a general format string in an Excel 2010 spreadsheet cell that needs to be converted to a corresponding calendar date and placed in next column. Any ideas on a VBA subroutine that would pass the converted date to a value in the next column or I can plug into an email. Ex. Cell A1 has string "week 01" and needs to be converted to March 5, 2012 and placed in B1, cell A2 has string "Week 02" and needs to be converted to March 12, 2012 and placed in B2 (second week of the month), A3 has "week 05" and needs to be converted to April 2, 2012 (5th week from beginning date March 5) an placed in B3.

Kuriakos,

Welcome to the forum!

Does it have to be a macro? You could get the same results with this formula in cell B1 and copied down (format cells as desired):

Please Login or Register  to view this content.

If it needs to be a macro though, this should work for you:

Please Login or Register  to view this content.

Alright, I am confused because there is conflicting information:

From your original post:

Originally Posted by Kuriakos

Ex. Cell A1 has string "week 01" and needs to be converted to March 5, 2012 and placed in B1, cell A2 has string "Week 02" and needs to be converted to March 12, 2012 and placed in B2 (second week of the month), A3 has "week 05" and needs to be converted to April 2, 2012 (5th week from beginning date March 5) an placed in B3.

From your reply:

Originally Posted by Kuriakos

Thanks for the welcome. Wow!! That was fast, however, the date returned in cell B2 and B3 are the same. If A1 = "Week 01" then A2 should be March 5, 2012, but if next row (B1) is "Week 07" then B2 should be April 23, 2012. Which is 7 weeks from March 5, 2012.

So in your original post, your data is setup like this:

Please Login or Register  to view this content.

And in your reply, the data is setup like this:

Please Login or Register  to view this content.

The information I posted earlier was going off of your original data setup. Does it instead need to go the other way?

Right, I would enter data in column A, and then input the formula and copy down. If the formula isn't pulling the correct results, make sure your workbook calculation is set to automatic:
Office Button -> Excel Options -> Formulas -> Select "Automatic" -> OK

You're very welcome, I'm glad it's working for you now