Short answer:
Use the following formula:
=DATE(MID(A1,8,4),MONTH(1&LEFT(A1,3)),MID(A1,5,2)) + IF(MID(A1, 22,2) = "PM", TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)) + TIME(12, 0, 0), TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)))
Long answer:
While not necessary, it's probably better to separate Time and Date
for data manipulation.
What I'd recommend is to use hidden columns, this makes your date nice and neatly separated, but furthermore will also help me explaining the answer rather than just pasting a giant formula:
Under presumption your text information always has the same format "Mon"/dd/yyyy hh:mm:ss AM/PM UTC
In cell B1, we'll paste our date:
=DATE(MID(A1,8,4),MONTH(1&LEFT(A1,3)),MID(A1,5,2))
Pretty self explanatory, we'll extract individual parts of the date format and apply it to them. The only trick part is using the MONTH()function to convert the String of Month to an actual Month format.
Now to the time, a bit more tricky:
Let's try to parse it in C1:
First, we'll need to implement an IF function, that will convert AM/PM format to 24h format and then we'll use string functions, similarly like in date above to extract data to our TIME() funciton.
=IF(MID(A1, 22,2) = "PM", TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)) + TIME(12, 0, 0), TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)))
Last but not least, in the column D we'll paste our result.
First make sure your format is set properly to your expected output:
and then we'll simply add our two data fields together:
=B1 + C1
Produces the expected result:
