Prev: how to apply Warning massage
Next: each cell have same number of charaters
From: Joe M. on 6 May 2010 08:44
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.





From: Gary''s Student on 6 May 2010 08:50
=DATE(LEFT(A4,4),MID(A4,5,256),1)
and format as mmm-yyyy
--
Gary''s Student - gsnu201002
From: Don Guillett on 6 May 2010 09:00
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Joe M." <JoeM(a)discussions.microsoft.com> wrote in message
news:EE0BE48E-47F7-4097-AFA6-E55DA8F33C3F(a)microsoft.com...
>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.
>
>
>
>
>

From: Joe M. on 6 May 2010 09:05
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
From: Gary''s Student on 6 May 2010 09:21
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
 |  Next  |  Last
Pages: 1 2
Prev: how to apply Warning massage
Next: each cell have same number of charaters