Convert Date string to date format
 

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.

Convert Date string to date format
 

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002

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

this will work.

all the best

Convert Date string to date format
 

Right click sheet tabview codeinsert 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.count1 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe M." wrote in message
...
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.

Convert Date string to date format
 

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" wrote:

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002

Convert Date string to date format
 

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

RIGHT(A4,2)
not so good......consider 20097
--
Gary''s Student - gsnu201002


"Joe M." wrote:

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" wrote:

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002

Convert Date string to date format
 

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

"Gary''s Student" wrote:

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

RIGHT(A4,2)
not so good......consider 20097
--
Gary''s Student - gsnu201002


"Joe M." wrote:

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" wrote:

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002

Convert Date string to date format
 

In that case, both your alternate colutions are good.
--
Gary''s Student - gsnu201002


"Joe M." wrote:

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

"Gary''s Student" wrote:

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

RIGHT(A4,2)
not so good......consider 20097
--
Gary''s Student - gsnu201002


"Joe M." wrote:

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" wrote:

=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002