date in text format to date format

Hello my friend.

Such a simple question.

Excel has a couple of simple tools that normally do the conversion.

But they don't work for your data.

So you are going to have to build a formula to do it.

Datevalue() will normally convert a date as text into a date that Excel understands. Unfortunately Excel only understands, 1, 01 or Jan not January.

So we have to get your date into a format that Datevalue undertands ie 19/1/2019

If A1 is: January 19, 2019 at 11:24PM

Then: MID(A1,FIND(" ",A1)+1,2) = 19

LEFT(A1,FIND(" ",A1)-1) = January

So:
MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)
Returns =1

MID(A1,FIND(",",A1)+2,4) = 2019

Put that all together with a couple of "/" then we get

=MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4)

This returns 19/1/2019

Put that into Datevalue returns = 43484 which is your date as Excel sees it.

=Datevalue(MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4))

'********************************************************************************************************************

With regards to the time.

RIGHT(A1,7) = 11:24PM

So LEFT(RIGHT(A1,7),5) = 11:24

Using Timevalue(LEFT(RIGHT(A1,7),5)) returns 0.475 which is 11:24AM as Excel sees it.

Obviously you need 11:24 PM so yo need to add 0.5 which is 12/24 ie 12 hours

+IF(RIGHT(A1,2)="PM",0.5,0)

So: Timevalue(LEFT(RIGHT(A1,7),5))+IF(RIGHT(A1,2)="PM",0.5,0) = 0.975 which is 23:24 as Excel sees it.

'************************************************************************************************************************

So you formula is:

=DATEVALUE(MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4))+TIMEVALUE(LEFT(RIGHT(A1,7),5))+IF(RIGHT(A1,2)="PM",0.5,0)

Which returns: 43484.975. There you are all done.

Sorry. You are saying "But That Does Not Look Like A Date!!!!"

Ok so you now need to format your cell so Excel knows to Display your number as Date and Time.

So Right Click on your cell

Select Format Cell
Select Custom

And paste this where it says Type: dd/mm/yyyy hh:mm then click on Ok.

Phewwwwwwww.

A slightly shorter version...
Assuming this text is in K12...
Date...
=DATEVALUE(LEFT(K12,FIND(" at",K12)-1))
Time...
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(K12,7),"PM"," PM"),"AM"," AM"))

Then just add them together...
=DATEVALUE(LEFT(K12,FIND(" at",K12)-1))+TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(K12,7),"PM"," PM"),"AM"," AM"))

Then format as above