Change Date format in Excel VBA

0
votes

I have the Date in the log file. The Date in the log file looks something like this :

Mon May 14 21:31:00 EDT 2012

I would like to convert this into the format "dd-MM-yyyy hh:mm:ss"

Dim DateVal1 As String 
Dim Date1 As Date

DateVal1 = "Mon May 14 21:31:00 EDT 2012"

Date1 = Format(DateVal,  "ddd mmm dd  hh:mm:ss EDT yyyy")
Date1 = Format(Date1 , "dd-MM-yyyy hh:mm:ss")

The following code is not converting into the format that I was expecting. Any Idea, If I am missing something here.

Thanks

2
vbaexcel
General issue in your question are month and weekday string definition: Mon May. That part would require some additional code. Will your months name go with full names (May, June, August) or with 3-letters abbreviation (May, Jun, Aug)? - Kazimierz Jawor
Just 3 letters as I see it - Kiran

2 Answers

1
votes

You might want to consider extracting datetime components from your custom datetime string first. For example, you can get day, month, year, etc. You could probably utilize string manipulation functions, like Mid, Replace and so on.

If your custom string is consistent, then you can create your own function that does string manipulations and outputs the datetime value.

Just a thought here. Hope it helps.

0
votes

If you are running English version of Office (and have English operating system) this function should give your results which you need:

Mon May 14 21:31:00 EDT 2012 >> 2012-05-14 21:31:00

Function DateConversion(DateVal1 As String)

    Dim DateParts As Variant
    DateParts = Split(DateVal1, " ")

    Dim i
    For i = 1 To 12
        If InStr(1, Left(MonthName(i), 3), DateParts(1), vbTextCompare) > 0 Then Exit For
    Next i

    DateConversion = CDate(DateSerial(DateParts(UBound(DateParts)), i, DateParts(2)) & " " & DateParts(3))

End Function

However, if you are using any other language it could require some additional changes referring to month names (MonthName function returns month names in your system language).

EDIT: Solution for situation of different languages month names

In this situation we need to create additional array with month names to be able to compare part of the original data string. 

Function DateConversionEng(DateVal1 As String)

    Dim DateParts As Variant
        DateParts = Split(DateVal1, " ")
    Dim Months As Variant
        Months = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "Octover", "November", "December")

    Dim i
    For i = 1 To 12
        If InStr(1, Months(i), DateParts(1), vbTextCompare) > 0 Then Exit For
    Next i

    DateConversionEng = CDate(DateSerial(DateParts(UBound(DateParts)), i + 1, DateParts(2)) & " " & DateParts(3))

End Function