Reputation: 8548
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
Upvotes: 0
Views: 9700
Reputation: 19077
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
Upvotes: 0
Reputation: 6455
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.
Upvotes: 1