Convert YMD string to a date

  • Thread starter Thread starter John Marshall, MVP
  • Start date Start date
J

John Marshall, MVP

How do I convert an eight character string in the form YYYYMMDD to a date?

John... Visio MVP
 
How do I convert an eight character string in the form YYYYMMDD to a date?

John... Visio MVP
Click to expand...

You need to insert the slashes:

DateValue(Left([textdate], 4) & "/" & Mid([textdate], 5, 2) & "/" &
Right([textdate], 2))
 
Dim StrYear As String
Dim StrMonth As String
Dim StrDay As String
StrYear = Left([EightChrString],4)
StrMonth = Mid([EightChrString,5,2)
StrDay = Right([EightChrString],2)
MyDate = DateSerial(CInt(StrYear),CInt(StrMonth),CInt(StrDay))
 
Thanks John, but how do indicate that it is ymd rather than ydm? The fact
that the first field is four characters gives it away as representing a
year, but their is no indication what the other two fields are.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
John Vinson said:
How do I convert an eight character string in the form YYYYMMDD to a date?

John... Visio MVP
Click to expand...

You need to insert the slashes:

DateValue(Left([textdate], 4) & "/" & Mid([textdate], 5, 2) & "/" &
Right([textdate], 2))
Click to expand...
 
Thanks.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
PC Datasheet said:
Dim StrYear As String
Dim StrMonth As String
Dim StrDay As String
StrYear = Left([EightChrString],4)
StrMonth = Mid([EightChrString,5,2)
StrDay = Right([EightChrString],2)
MyDate = DateSerial(CInt(StrYear),CInt(StrMonth),CInt(StrDay))


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


John Marshall said:
How do I convert an eight character string in the form YYYYMMDD to a date?

John... Visio MVP
Click to expand...
Click to expand...
 
Thanks John, but how do indicate that it is ymd rather than ydm?
Click to expand...

Hrm. I've never seen ydm as a date format, and in practice it works -
but you're right, I can't guarantee that it will do so for all
regional settings! The DateSerial() approach elsethread will certainly
do the trick.
 
It turns out that the reason I could not get my text to date function to
work is because the variable passed to the routine was a reserved word.
Changed the variable and everything works.

I doubt ydm is used, but there is confusion with dmy and mdy.

John... Visio MVP
 
You know how to convert the yyyymmdd to a date. Use CDate to convert the
hh:mm to a time, then add the two together. I think this will fail if you're
dealing with dates prior to 31 Dec, 1899.

Date/times are stored as 8 byte floating point numbers, where the date is
represented as the number of days relative to 30 Dec, 1899, and the time is
represented as a fraction of a day. Access thinks that a time-only value is
really that time on 30 Dec, 1899. However, it isn't smart enough to object
to you adding the date and time together. <g>
 
Back
Top