vba script to convert datetime string to date format

[Beatrix]

Hi Everyone ,

I am looking for a script to convert date time string to date format. Can anyone help on this?:help

Please see column A in attached spreadsheet.

From:

2011-Sep-15 12:00:00


To:

15/09/2011

Cheers
Yeliz

[Paul_Hossler]

I'd use a User Defined Function to put the dates into another column. The new column needs to have the proper number formats.



Option Explicit

Function Str2Date(s As String) As Variant
Dim y As Long, m As Long, d As Long

On Error GoTo NiceExit
'12345678901234567890
'2011-Sep-15 12:00:00
y = Mid(s, 1, 4)
Select Case LCase(Mid(s, 6, 3))
Case "jan": m = 1
Case "feb": m = 2
Case "mar": m = 3
Case "apr": m = 4
Case "may": m = 5
Case "jun": m = 6
Case "jul": m = 7
Case "aug": m = 8
Case "sep": m = 9
Case "oct": m = 10
Case "nov": m = 11
Case "dec": m = 12
End Select
d = Mid(s, 10, 2)

Str2Date = DateSerial(y, m, d)

Exit Function

NiceExit:

Str2Date = CVErr(xlErrNA)
End Function



Assumes that the inputs are fixed position (faster that way). Could be changed

You could also make a sub that just reformats Col A, but that would not be (easily) undo-able

Paul

[snb]

or

=TEXT(LEFT(A11,5)&MATCH(MID(A11,6,3),{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)&MID(A11,9,3);"dd/mm/yyyy")

or


Function F_date_snb(c00)
F_date_snb = Int(CDate(Replace(c00, Mid(c00, 6, 3), Format(Application.Match(Mid(c00, 6, 3), Application.GetCustomListContents(3), 0)))))
End Function

[Paul_Hossler]

Application.GetCustomListContents(3),



THAT's what I couldn't remember:doh:


Paul

[snb]

I also had to look it up :

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[Beatrix]

Thanks very much Paul. Is this for to make a sub ?

Cheers
Yeliz

[Beatrix]

Thanks very much for your help snb:cloud9:



or

=TEXT(LEFT(A11,5)&MATCH(MID(A11,6,3),{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)&MID(A11,9,3);"dd/mm/yyyy")

or


Function F_date_snb(c00)
F_date_snb = Int(CDate(Replace(c00, Mid(c00, 6, 3), Format(Application.Match(Mid(c00, 6, 3), Application.GetCustomListContents(3), 0)))))
End Function

[Paul_Hossler]

Updated to include SNB's better approach, but this is a user defined function still. Included in your example



Option Explicit
Function Str2Date(s As String) As Variant
Dim y As Long, m As Long, d As Long

On Error GoTo NiceExit
'12345678901234567890
'2011-Sep-15 12:00:00
y = Mid(s, 1, 4)
With Application
m = .WorksheetFunction.Match(Mid(s, 6, 3), .GetCustomListContents(3), 0)
End With
d = Mid(s, 10, 2)

Str2Date = DateSerial(y, m, d)

Exit Function

NiceExit:

Str2Date = CVErr(xlErrNA)
End Function




Paul

[snb]

Why do you use variables ?


Function F_str2date_snb(c00)
F_str2date_snb = CDate(Mid(c00, 10, 2) & "-" & Application.Match(Mid(c00, 6, 3), Application.GetCustomListContents(3), 0) & "-" & Mid(c00, 1, 4))
End Function

[Beatrix]

I used the first option below but formula doesn't recognise it as a date in this COUNTIF(A2:A11,"> 01/09/2012"):dunno



or

=TEXT(LEFT(A11,5)&MATCH(MID(A11,6,3),{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)&MID(A11,9,3);"dd/mm/yyyy")

or


Function F_date_snb(c00)
F_date_snb = Int(CDate(Replace(c00, Mid(c00, 6, 3), Format(Application.Match(Mid(c00, 6, 3), Application.GetCustomListContents(3), 0)))))
End Function

[Beatrix]

ok this is working perfect!! Thanks guys.. I appreciate for your time :bow:


Updated to include SNB's better approach, but this is a user defined function still. Included in your example



Option Explicit
Function Str2Date(s As String) As Variant
Dim y As Long, m As Long, d As Long

On Error GoTo NiceExit
'12345678901234567890
'2011-Sep-15 12:00:00
y = Mid(s, 1, 4)
With Application
m = .WorksheetFunction.Match(Mid(s, 6, 3), .GetCustomListContents(3), 0)
End With
d = Mid(s, 10, 2)

Str2Date = DateSerial(y, m, d)

Exit Function

NiceExit:

Str2Date = CVErr(xlErrNA)
End Function




Paul

[SamT]

Sub ConvertDates()

Dim Old As Range
Dim Cel As Range
Set Old = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'Range formating required to fix some goofy results. YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With

'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub

[Beatrix]

much appreciated!! Thanks Sam:cloud9:



Sub ConvertDates()

Dim Old As Range
Dim Cel As Range
Set Old = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'Range formating required to fix some goofy results. YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With

'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub

[Paul_Hossler]

Why do you use variables ?


Personal style

I find it easier to follow the code months later when I'm 'wordy'

My Opinion: Internally I don't think there's any performance penalty since VBA still has to push and pop all the same things to the call stack anyway

Paul

[Beatrix]

Hi Guys ,

I want to mark this thread as solved but can't find it in Thread tools as navigation changed.Any ideas??

[Beatrix]

Hi Sam ,

It takes two sec when I run the code in attached spreadsheet however takes ages when I run it in original workbook with multiple worksheets.
I think I need to edit below line to specify worksheet called Data. I tried by adding Worksheets("Data") just before the range but didn't work:doh:


Set Old = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

Yeliz





Sub ConvertDates()

Dim Old As Range
Dim Cel As Range
Set Old = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'Range formating required to fix some goofy results. YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With

'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub

[snb]

sub M_snb()
for each sh in sheets
sh.activate
[A2:A2000]=iif(A2:A2000="","",TEXT(LEFT(A2:A2000,5)&MATCH(MID(A2:A2000,6,3),{"jan","feb","mar","apr","may","jun (A2@:A2000,5)&MATCH(MID(A11,6,3),{"jan","feb","mar","apr","may","jun)"," jul","aug","sep","oct","nov","dec"},0)&MID(A@:A2000,9,3),"dd/mm/yyyy"))]
next
End sub

[Beatrix]

Thank you all for all your replies. I just want to learn how to edit below code if I run it for a specific worksheet?:dunno



Sub ConvertDates()

Dim Old As Range
Dim Cel As Range
Set Old = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'Range formating required to fix some goofy results. YMMV
'Try it with nd without the next 4 lines
With Old
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlLeft
End With

'2011-Sep-15 12:00:00 to 15/09/2011
For Each Cel In Old
Cel = Format(Mid(Cel, 6, 3) _
& "/" & Mid(Cel, 10, 2) _
& "/" & Left(Cel, 4), "dd/mm/yyyy")
Next Cel
End Sub

[mancubus]

try adding ws name before range.
Set Old = Worksheets("WSNameHere").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)