date formatting

[Guest]

Hi ,
I got a string like "20030101" and I want this to become the date 01 jan 03 . I can't figure it out. Can you please help if this action is possible

Cheers,
Douvid

 

[J.E. McGimpsey]

One way:

Select your date string(s). Choose Data/Text to Columns. Click Next,
Next. Select YMD in the date dropdown. Click Finish. Format as a
date.


Note: Your example is ambigouous, so you may need to use YDM instead.

 

[Guest]

ok but I'm want to do all that from vba. through coding

 

[Tom Ogilvy]

Turn on the macro recorder and do it manually. (for Text to columns)

If you are talking about parsing a string (assuming string is yyyymmdd)

Dim dt as Date
Dim sStr as String
sStr = "20030101"
dt = DateValue(Mid(sStr,5,2) & "/" & mid(sStr,7,2) & "/" & Left(sStr,4))

Demo'd from the immediate window:
sStr = "20030101"
? DateValue(Mid(sStr,5,2) & "/" & mid(sStr,7,2) & "/" & Left(sStr,4))
1/1/03