str to date

K

Ken Snell

ConvertedDate = Right("YYYYMMDD", 2) & "/" & Mid("YYYYMMDD", 5, 2) & "/" &
Left("YYYYMMDD", 4)
 
V

Victor Delgadillo

Would this work better?:

ConvertedDate = CDate(Mid("YYYYMMDD", 5, 2) & "/" & Right("YYYYMMDD", 2) &
"/" & Left("YYYYMMDD", 4))

This will give a string mm/dd/yyyy and convert it to a julian date.
I think the original had the format dd/mm/yyyy which would not be the
correct US Date.
 
K

Ken Snell

The poster didn't indicate the intended use of the converted date...but if
he wants to use the converted date in a query, then he should use your
approach. Thanks.
 
V

Van T. Dinh

Actually, I think to avoid the ambiguity of date formats,
the DateSerial function would be the best one.

Assuming the DateString is "YYYYMMDD", the expression:

ConvertedDate = DateSerial ( _
CInt(Left(DateString, 4)), _
CInt(Mid(DateString, 5, 2), _
CInt(Right(DateString, 2) )

will remove the ambiguities created by Date / Time
Regional settings.

HTH
Van T. Dinh
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Converting Birth Date format 1
How to add day to PWA 2016 date format? 0
Date Formats 4
printing report with date 2
Publisher is filling blank lines with data 2
How to enter a date in DD-MM-YYYY format 2
Forcing a date entry format of dd/mm/yyyy 1

Top