Convert string to date

[zSplash]

Sorry this is so sad, but it's been awhile: I have a 6-digit string
representing a date. That is, "123005" has been input. It means 12/30/05,
but it's defined as a string. I want to convert it to the date value for
12/30/05 (not for 10/09/2236).

How do I convert this string to its complementary date value?

TIA

 

[Perry]

Lookup following VBA functions in VBE Help:
Left()
Mid()
Right()
DateSerial()
Format()

Kindly repost if you can't make progress, indicating where you got stuck.

Krgrds,
Perry

 

[macropod]

Hi zSplash,

You could try something along the lines of:

Sub Test()
Dim Dt As String
Dim oDt As Date
Dt = "123005"
oDt = CDate(Mid(Dt, 1, 2) & "/" & Mid(Dt, 3, 2) & "/" & Mid(Dt, 5, 4))
MsgBox oDt & vbCrLf & FormatDateTime(oDt, vbLongDate)
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


| Sorry this is so sad, but it's been awhile: I have a 6-digit string
| representing a date. That is, "123005" has been input. It means 12/30/05,
| but it's defined as a string. I want to convert it to the date value for
| 12/30/05 (not for 10/09/2236).
|
| How do I convert this string to its complementary date value?
|
| TIA
|
|

 

[Tony Jollans]

Why not ...

DateValue(Format("123005","00/00/00"))

 

[Helmut Weber]

Hi Tony,

beware!

There are large chapters in some of my books,
about conversion to and from date,
and none of them seems to cover all.

As far as I know, there are even differences
between the UK and the US.

Debug.Print DateValue(Format("123005", "00/00/00")) ' 2005-12-30
Debug.Print DateValue(Format("121105", "00/00/00")) ' 2012-11-05

Slash versus hyphen may be disregarded,
due to my regional date and time settings.

I got an US-Version of Windows here.
In order to be compatible, I thought,
I had eliminated all localization issues.
Nevertheless, time and date settings
sometimes interfere in a non-predictable way.

I even get dialogs in german,
where I never expected it.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Tony Jollans]

You are correct, Helmut.

Assuming all the dates are this century, one could use:

DateValue(Format("123005", "00/00/2\000"))

And assumptions about day and month *should* be based on regional settings.

But one must certainly take care.

Although I could probably cope I haven't yet seen a German Dialog in my
English Windows <g>