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?

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


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

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")

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

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

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

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"," 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?

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)