Convert alpha-numeric code to date

I need to convert several alpha-numeric codes into a month and year.

The code format is as follows:

DC22G02292

The first character "D" is ignored. The second charater C represents the year and is defined as A=2007, B=2008, C=2009, D=2010, etc.

The next two characters (22) stand for the week of the year. Charaters 5-10 are ignored.

In this example the month and year would be May 2009.

Is there a way to create a formula in Excel that will make this conversion?

A number of weeks straddle two months by various numbers of days. What are the rules for weeks relative to months?

At first, I thought you were using a 4-4-5 fiscal quarter method...but, evidently not.
Try this:

=TEXT(DATE(SEARCH(MID(A1,2,1),"ABCDEFGHIJKL")+2006,MATCH(--MID(A1,3,2),{1,5,9,13,18,22,26,31,35,740,44,48},1),1),"mmmm yyyy")

Is that something you can work with?

Ron - looks like this is going to work just fine! I have one more step to go that is:

I need to calculate the number of months between two different date formats. An example of the first format is :

3/28/2008

The second format is what we determined previousy, only a month and year:

June 2010

Is there an easy way to caluclate the number of months between 3/28/2008 and June 2010?

Thanks!

Hi Buchu,

I had your DC22G02292 in C18 - but I got June 2009 with your criteria:

Function MoYr(R As Range) As String
Dim Y As String, W As Integer, n As Integer
Y = Mid(R, 2, 1): W = Mid(R, 3, 2)
Dim Yr As Integer, Mo As String, Alpha
Alpha = Array(" ", "A", "B", "C", "D", "E", "F", "G", _
"H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
"R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For n = 1 To 26
If Alpha(n) = Y Then Exit For
Next n
Yr = 2006 + n
If W < 53 Then Mo = "December"
If W < 48 Then Mo = "November"
If W < 44 Then Mo = "October"
If W < 40 Then Mo = "September"
If W < 35 Then Mo = "August"
If W < 31 Then Mo = "July"
If W < 26 Then Mo = "June"
If W < 22 Then Mo = "May"
If W < 18 Then Mo = "April"
If W < 13 Then Mo = "March"
If W < 9 Then Mo = "February"
If W < 5 Then Mo = "January"

MoYr = Mo & " " & Yr
End Function

Sub TestMoYr(): Dim S As String
S = MoYr(Range("C18"))
End Sub

Hi Buchu,

You need to put the function in a standard module then you can use it just like any other Worksheet function.

DC22G02292	=MoYr(C18)

DC22G02292	June 2009

Hello Buchu

right-click on the tab
choose - view code
in the window that will appear click insert menu - choose module
thenpaste the code given above by xladept in the window.close.
then you can use
=MoYr(C18)

btw save the workbook as macro-enabled workbook.

Hi Buchu,

Normal View:

3/28/2008	June 2012	50

Formula View:

39535	41061	=(K12-J12)*12/365

Does this work for you?

Hi Buchu,

Thanks for the function - Good Luck!