convert general date column to true date/time

ive received an extract from data with a column which should represent date/time, it is currently formatted 'general', in the format:

2018-02-23 11:58:25 AM GMT

What is the safest way to convert it to a true date/time so I can do some date filters?
LVL 3
pma111Asked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
Hi,
Are you looking for an excel formula or a VBA solutions ?

In the case of VBA, I would use a couple of function to test if the value is a date (with the help of regex), and do the convertion is needed:
Option Explicit

    '// check the value is a date/time
Public Function isDate(ByVal value As String) As Boolean
    isDate = isDateFormat(value) And isDateValue(value)
End Function

    '// check the value is a date/time format
Public Function isDateFormat(ByVal value As String) As Boolean
    Dim rx As Object            '// VBScript_RegExp_55.RegExp
    Set rx = CreateObject("VBScript.RegExp")
    
    rx.Global = True
    rx.MultiLine = False
    rx.Pattern = "^\d{4}-(?:0[1-9]|1[012])-(?:0[1-9]|[12]\d|3[01]) (?:[01]\d|2[0-3]):(?:[0-6]\d):(?:[0-6]\d) [AP]M GMT$"
    isDateFormat = rx.test(value)
    Set rx = Nothing
End Function

    '// check the value is a valid date/time
    '// in other words: if the calculated value is equal to the value given as parameter
Public Function isDateValue(ByVal value As String) As Boolean
    Dim rx As Object            '// VBScript_RegExp_55.RegExp
    Set rx = CreateObject("VBScript.RegExp")
    
    rx.Global = True
    rx.MultiLine = False
        '// same regex as above, cept we capture date and time data
    rx.Pattern = "^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12]\d|3[01]) ([01]\d|2[0-3]):([0-6]\d):([0-6]\d) ([AP]M) GMT$"
    
    Dim matches As Object       '// VBScript_RegExp_55.MatchCollection
    Set matches = rx.Execute(value)
    
    Dim subMatches As Object    '// VBScript_RegExp_55.subMatches
    Set subMatches = matches(0).subMatches
    
    Dim dt As Date
    dt = DateSerial(CInt(subMatches(0)), CInt(subMatches(1)), CInt(subMatches(2)))
    If (subMatches(6) = "AM") Then
        dt = dt + TimeSerial(CInt(subMatches(3)), CInt(subMatches(4)), CInt(subMatches(5)))
    Else
        dt = dt + TimeSerial(CInt(subMatches(3)) + 12, CInt(subMatches(4)), CInt(subMatches(5)))
    End If
    Set subMatches = Nothing
    Set matches = Nothing
    Set rx = Nothing
    
    isDateValue = value = (Format(dt, "yyyy-mm-dd [$-en-US]hh:mm:ss AM/PM") & " GMT")
End Function

    '// convert to a date/time
Public Function toDate(ByVal value As String) As Date
    Dim rx As Object            '// VBScript_RegExp_55.RegExp
    Set rx = CreateObject("VBScript.RegExp")
    
    rx.Global = True
    rx.MultiLine = False
    rx.Pattern = "^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12]\d|3[01]) ([01]\d|2[0-3]):([0-6]\d):([0-6]\d) ([AP]M) GMT$"
    
    Dim matches As Object       '// VBScript_RegExp_55.MatchCollection
    Set matches = rx.Execute(value)
    
    Dim subMatches As Object    '// VBScript_RegExp_55.subMatches
    Set subMatches = matches(0).subMatches
    
    Dim dt As Date
    dt = DateSerial(CInt(subMatches(0)), CInt(subMatches(1)), CInt(subMatches(2)))
    If (subMatches(6) = "AM") Then
        dt = dt + TimeSerial(CInt(subMatches(3)), CInt(subMatches(4)), CInt(subMatches(5)))
    Else
        dt = dt + TimeSerial(CInt(subMatches(3)) + 12, CInt(subMatches(4)), CInt(subMatches(5)))
    End If
    Set subMatches = Nothing
    Set matches = Nothing
    Set rx = Nothing
    toDate = dt
End Function

Open in new window

Sample test:
Public Sub test()
    Const dt As String = "2018-02-23 11:58:25 AM GMT"
    If (isDate(dt)) Then
        Debug.Print toDate(dt)
    Else
        Debug.Print "invalid date."
    End If
End Sub

Open in new window

0
 
pma111Author Commented:
Either really.
0
 
Fabrice LambertFabrice LambertCommented:
In addition to the VBA functions, you can use the following formulas in your worksheet:
=SI(isDate(A1);toDate(A1);"")
According your date is in the A1 cell, and format your resulting cell to a date.
0
 
Matt NicholasBusiness AnalystCommented:
Use Power Query to convert the data to date/time with locale information - no code required
0
 
Rob HensonFinance AnalystCommented:
With the date string in A2, the following formula will convert to a valid date:

=DATEVALUE(LEFT(A2,10))   format as date format required eg "dd/mm/yy"  or "mm/dd/yy" if preferred in your locale

If you want date and time:

=VALUE(LEFT(A2,19))  custom format as "dd/mm/yy hh:mm"  or  "mm/dd/yy hh:mm"

If you want date and time in separate columns:

Date column, as above:
=DATEVALUE(LEFT(A2,10))

Time column:
=MOD(VALUE(LEFT(A2,19)),1)  format as "hh:mm"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.