Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

try

Sub getdatefromstring()
For Each c In range("a2:a22")'Selection
x = Mid(c, InStrRev(c, "-") + 1, 4)
md = Format(DateSerial(2006, Left(x, 2), _
Right(x, 2)), "mm/dd/yyyy")
MsgBox md
Next c
End Sub


--
Don Guillett
SalesAid Software

"c8tz" wrote in message
oups.com...
Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

OR to get May 2002

Sub getdatefromstring()
For Each c In Selection
x = Mid(c, InStrRev(c, "-") + 1, 4)
md = Format(DateSerial(Right(x, 2), Left(x, 2), 1), "mmm yyyy")
MsgBox md
Next c
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try

Sub getdatefromstring()
For Each c In range("a2:a22")'Selection
x = Mid(c, InStrRev(c, "-") + 1, 4)
md = Format(DateSerial(2006, Left(x, 2), _
Right(x, 2)), "mm/dd/yyyy")
MsgBox md
Next c
End Sub


--
Don Guillett
SalesAid Software

"c8tz" wrote in message
oups.com...
Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

Hi

Probably rather longwinded, but it's late!!
With string in A1

=--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2)
&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2))

FormatCellsNumberCustommmm yy
--
Regards

Roger Govier


"c8tz" wrote in message
oups.com...
Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

On Feb 2, 11:17 am, "Roger Govier"
wrote:
Hi

Probably rather longwinded, but it's late!!
With string in A1

=--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2)
&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2))

FormatCellsNumberCustommmm yy
--
Regards

Roger Govier

"c8tz" wrote in message

oups.com...



Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~- Hide quoted text -

- Show quoted text -

Hi,

I figured out one way ... but it puts the year as 1902 and not 2002 -

=DATE(MID(F2,10,2),MID(F2,8,2),1)

it picks up 02 as 1902 which should be 2002 -

can anyone expand from my formula ??

Thanks - meanwhile I'll try the other two -

thanks alot!

Hi
=DATE(MID(F2,10,2),MID(F2,8,2),1)
If your strings are always going to be of fixed length, then you only
need to deal with the century within your formula.

Taking the easy solution first, if all dates are after 2000, then
=DATE(MID(F2,10,2)+100,MID(F2,8,2),1)

If there can be dates up to 1999, but after 1950, then
=DATE(MID(F2,10,2)+100*(MID(F2,10,2)<"50"),MID(F2, 8,2),1)

--
Regards

Roger Govier


"c8tz" wrote in message
ups.com...
On Feb 2, 11:17 am, "Roger Govier"
wrote:
Hi

Probably rather longwinded, but it's late!!
With string in A1

=--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2)
&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2))

FormatCellsNumberCustommmm yy
--
Regards

Roger Govier

"c8tz" wrote in message

oups.com...



Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 -
how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~- Hide quoted text -

- Show quoted text -

Hi,

I figured out one way ... but it puts the year as 1902 and not 2002 -

=DATE(MID(F2,10,2),MID(F2,8,2),1)

it picks up 02 as 1902 which should be 2002 -

can anyone expand from my formula ??

Thanks - meanwhile I'll try the other two -

thanks alot!

This should do it but if your text is not always the same use my last post
macro
=DATE("20"&MID(E2,10,2),MID(E2,8,2),1)

--
Don Guillett
SalesAid Software

"c8tz" wrote in message
ups.com...
On Feb 2, 11:17 am, "Roger Govier"
wrote:
Hi

Probably rather longwinded, but it's late!!
With string in A1

=--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2)
&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2))

FormatCellsNumberCustommmm yy
--
Regards

Roger Govier

"c8tz" wrote in message

oups.com...



Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~- Hide quoted text -

- Show quoted text -

Hi,

I figured out one way ... but it puts the year as 1902 and not 2002 -

=DATE(MID(F2,10,2),MID(F2,8,2),1)

it picks up 02 as 1902 which should be 2002 -

can anyone expand from my formula ??

Thanks - meanwhile I'll try the other two -

thanks alot!

Assuming all of your data follows the same format as your example, this
formula should work:

=MID(A1,LEN(A1)-5,4)

Another option would be:

=MID(A1,FIND("-",SUBSTITUTE(A1,"-","",1))+2,4)

HTH,
Elkar



"c8tz" wrote:

Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

On 1 Feb 2007 16:59:27 -0800, "c8tz" wrote:

Hi,

I have a string eg.125-64-0502L where 0502 represents May 2002 - how
can i specify that without doing so manually.

thanks in advance for your assistance,

c8tz~

What do you mean by "specify that"?

If you mean to extract the last four digits and interpret them in terms of
month and year, then this formula should work:

=--TEXT(MID(A1,LEN(A1)-4,4),"00""/01/""00")

will return a serial date. Then format the cell:

Format/Cells/Number/Custom Type: mmm yyyy


Or, if you just want text, and not a serial date:

=TEXT(--TEXT(MID(A1,LEN(A1)-4,4),"00""/01/""00"),"mmm yyyy")

If you mean something else, please be more specific.

Best,
--ron