Convert Date string to date format

J

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.
 
B

bala_vb

Joe said:
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
 
D

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
 
J

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!
 
G

Gary''s Student

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top