Convert Date string to date format

[Joe M.]

I have some date strings I need to convert to date format. For example im
trying to convert strings like "200910" to Oct-2009. I tried using something
like this to do it but I get an error:

Cell A4: "200910" this is the date string to be converted
Cell B4: =left(a4,4)&","&right(a4,2)&","&"01" changes to "2009,10,01"
Cell C4: =date(b4)
After converting to a date I would use the custom cell format "mmmm-yyyy" in
C4 to give the result of Oct-2009.

I get the error "You've entered too few arguments for this function"

Can someone help?

Thanks,
Joe M.

 

[Gary''s Student]

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy

 

[bala_vb]

I have some date strings I need to convert to date format. For exampl
im
trying to convert strings like "200910" to Oct-2009. I tried usin
something
like this to do it but I get an error:

Cell A4: "200910" this is the date string to be converted
Cell B4: =left(a4,4)&","&right(a4,2)&","&"01" changes to "2009,10,01"
Cell C4: =date(b4)
After converting to a date I would use the custom cell forma
"mmmm-yyyy" in
C4 to give the result of Oct-2009.

I get the error "You've entered too few arguments for this function"

Can someone help?

Thanks,
Joe M.

Try this formulae in B4 cell.
=TEXT(DATE(LEFT(A4,4),RIGHT(A4,2),1),"MM-YYYY")

this will work.

all the bes

 

[Don Guillett]

Right click sheet tab>view code>insert this. Now when you type 200910 into a
cell in column A it will be changed to the format desired in the same cell.
If you really want it in c then use offset

Private Sub Worksheet_Change(ByVal Target As Range)
it target.count>1 or target.column<> 1 then exit sub
Application.EnableEvents = False
Target.Value = DateSerial(Left(Target, 4), Right(Target, 2), 1)
Target.NumberFormat = "mmmm yyyy"
Application.EnableEvents = True
End Sub

 

[Joe M.]

It works great! But I don't understand why its necessary to use 256 in
MID(A4,5,256) instead of MID(A4,5,2) or even RIGHT(A4,2). Maybe you can shed
some light on that. Thanks!

 

[Gary''s Student]

You are correct!
MID(A4,5,2) is just as good.

RIGHT(A4,2)
not so good......consider 20097

 

[Joe M.]

All my date strings have 2 charactors for the month so "7" would be 200907...

 

[Gary''s Student]

In that case, both your alternate colutions are good.