PDA

View Full Version : Convert String To Date



youngmcc
03-30-2011, 03:36 AM
I have a procedure which reconciles text strings.

The values are stored as Details(2) and Details(3) and are in format DDMMYYYY (30032011)

On the rec output, rather than passing in the variable in this format, I was hoping to convert to a date format such as DD-MMM-YYYY (30-Mar-2011)

Is anyone able to help?

Atravis
03-30-2011, 05:11 AM
Hi the function below should return what i think you want.
As far as i am aware you will first need to break up your string into a recognized date, so i have put "/" in, then you can use the format function to get the month to come out as text.


Function date_format(date_used As String)
'Change input into recognized date format eg. 30032011 goes to 30/03/2011
date_used = Left(date_used, 2) & "/" & Mid(date_used, 3, 2) & "/" & Right(date_used, 4)
'change to required format eg. 30/03/2011 goes to 03-Mar-2011
date_format = Format(date_used, "DD-MMM-YYYY")

End Function

mdmackillop
03-30-2011, 05:30 AM
Have a look at this recent KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1118)which uses a similar method

youngmcc
03-30-2011, 07:06 AM
Hi the function below should return what i think you want.
As far as i am aware you will first need to break up your string into a recognized date, so i have put "/" in, then you can use the format function to get the month to come out as text.


Function date_format(date_used As String)
'Change input into recognized date format eg. 30032011 goes to 30/03/2011
date_used = Left(date_used, 2) & "/" & Mid(date_used, 3, 2) & "/" & Right(date_used, 4)
'change to required format eg. 30/03/2011 goes to 03-Mar-2011
date_format = Format(date_used, "DD-MMM-YYYY")

End Function


Works a treat.

Thanks very much.

BrianMH
03-30-2011, 08:49 AM
Just keep in mind that both the function in the kb article and this function assumes your region date setting uses the dd/mm/yyyy format vs the US mm/dd/yyyy format. So for in one instance 02032010 will return 02-MAR-2010 and in another instance 02-FEB-2010.

A way around this would be.

Function date_format(date_used As String) as date
dim year as string
dim month as string
dim day as string
year = Right(date_used, 4)
month = Mid(date_used, 3, 2)
Day = Left(date_used, 2)
select case Application.International(xlDateOrder)
case 0
dateused = datevalue(month & "/" & day & "/" & year)
case 1
dateused = datevalue(day & "/" & month & "/" & year)
case 2
dateused = datevalue(year & "/" & month & "/" & day)
end select
date_format = format(dateused,"DD-MMM-YYYY")

End Function
mdmackillop is there a way I can update my kb entry?

mdmackillop
03-30-2011, 09:27 AM
Returned to WIP for editing

BrianMH
03-30-2011, 09:45 AM
Thanks

Edited and resubmitted.

youngmcc
03-30-2011, 03:16 PM
Brian,

Thanks for pointing that out.
I'll take a look at the code.

How does VBA know which case statement to use if its passed in as a string?

Im eager to learn so all of this information is greatly appreciated.

Thanks

McC

BrianMH
03-30-2011, 11:07 PM
Application.International(xlDateOrder) returns 0 or 1 or 2 based on the date order setting in the application.

0 is the US MM/DD/YYYY
1 is the EU DD/MM/YYYY
2 is the international YYYY/MM/DD

youngmcc
03-31-2011, 01:54 AM
Application.International(xlDateOrder) returns 0 or 1 or 2 based on the date order setting in the application.

0 is the US MM/DD/YYYY
1 is the EU DD/MM/YYYY
2 is the international YYYY/MM/DD

Thats great.
Thanks for clearing that up for me.