Convert Date string to date format

  • Thread starter Thread starter Joe M.
  • Start date Start date
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.
 
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.
Click to expand...

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

this will work.

all the bes
 
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
 
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!
 
You are correct!
MID(A4,5,2) is just as good.

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