Pfthb

Is there any way to format the following text to date and time format in Excel? Nothing I try works.

Fri, November 16 2018 8:00 PM

I have tried doing the following, with no luck.

=TEXT("Fri, November 16 2018 8:00 PM","ddd, mmm d yyyy h:mm AM/PM")

If you want to avoid VBA, then you need to use the DATEVALUE and TIMEVALUE functions. Unfortunately these functions aren't smart enough to convert the text you describe, but you can still split up the text with cell formula prior to inputting into these functions.
The following formula will convert the text Fri, November 16 2018 8:00 PM to a date/time value (assuming text is in cell A1).

=DATEVALUE(SUBSTITUTE(MID(A1, FIND(" ",A1)+1, FIND("|", SUBSTITUTE(A1," ","|",4))-FIND(" ",A1)), " ", ", ", 2)) + TIMEVALUE(RIGHT(A1, LEN(A1)-FIND("|", SUBSTITUTE(A1," ","|",4))))

You can create your own custom UDF and use it like a worksheet function:

Public Function myDateToDate(inputRng As Range) As Date

 With CreateObject("VBScript.RegExp")
 .Pattern = "w3,s(w+sd+sd+)s(d+:d+sw+)"
 If .test(inputRng.Value) Then
 With .Execute(inputRng.Value)(0).SubMatches
 myDateToDate = CDate(.Item(0)) + CDate(.Item(1))
 End With
 End If
 End With

End Function

This will use Regular Expressions (RegEx) to grab the values in your string, and capture both the date and the time. Since within Excel all a date is is a number, you can add the first capturing group (w+sd+sd+) (which represents the date) with the second capturing group (d+:d+sw+) (which represents the time) together. (See how the regex pattern works here).

After you have done this, you can now use your newly-created UDF by using the worksheet formula =myDateToDate(A1) - where A1 would be replaced with the cell/string that contains your date.

If you are unfamiliar with VBA, read on:

How do I access the VBE?

You can gain access to VBE by pressing Alt + F11 while you are inside your workbook.

Okay, So I have the VBE open. Now how do I apply this Sub/UDF?

In the left pane you will see your workbook object modules. This is called the Project Explorer. You will need to create a new module, which you can do by right-clicking inside the Project Explorer > Insert > Module:

Now you can paste the Sub/UDF to this new module and you are all set!

A monstrosity but should be locale independent (either US or UK):

=DATE(LEFT(RIGHT(A1,13),5),MONTH(DATEVALUE(LEFT(MID(A1,6,9),FIND(" ",MID(A1,6,10)))&"1")),MID(MID(A1,FIND(" ",A1)+1,99),FIND(" ",MID(A1,FIND(" ",A1)+1,99))+1,2))+LEFT(TRIM(RIGHT(A1,8)),7)

Nice regex solution above, you can also do it in a different way:

Function ConvertToDate(str As String) As Date

 Dim arr() As String

 arr = Split(str, " ")

 ConvertToDate = CDate(arr(3) & "/" & Month("01/" & arr(1) & "/2018") & "/" & arr(2) & " " & arr(4))

End Function