USA Date Time AM & PM convert to UK Time 24hr

[ViperStripes]

I thought I was pretty good at managing Date formats in Excel but this one has left me stumped.

The raw data field in the csv pre import is in the USA format and complicated by being AM/PM too.
,"8/30/2021 7:39:07 AM",
,"8/29/2021 11:28:40 PM",
All other columns are simple numeric or text

How to I get Excel to recognise these as dates in UK format: dd/mm/yyyy hh:mm:ss in one column not 2.
I can import it as 3 separate columns delimited by space with the 1st column set as MDY which imports the date part correctly, but that leaves me with the time spread over 2 columns, which needs further faffing to bring it back together then convert to 24hr then add back to the date.
I can use find and substitute to extract the values for date(year(), month(), day()) but that thinks it's 1905 not 2021
and various other extraction methods that are faffy and also only half a solution.

 

[ViperStripes]

Dave Patton:
The table above are all original dataset and hence are back to front i.e. m/d when using your formula on them it returns a #value error. All other 2000 rows convert correctly with your formula.

 

[ViperStripes]

Done some more examination, the ones that aren't converting with DavePotton's formula are all:
LEN = 21
AND
double digit month, single digit day AND 11 digits for the time portion i.e. 1x:xx:xx XM

If it has that combo of factors then the formula fails. i.e. Top 2 rows below work, bottom 2 fail.

10/7/2020 1:48:14 PM	len = 20	07/10/2020 13:48​
10/8/2020 2:35:57 PM	len = 20	08/10/2020 14:35​
10/2/2020 10:45:33 AM	len = 21	#VALUE!​
10/2/2020 10:46:01 AM	len = 21	#VALUE!​

 

[ViperStripes]

So working this out step by step, the If len bit of the formula needs adjusting, everything else works:

IF(LEN(L2002)>20,2,1)
d/m/yyyy	9​
dd/mm/yyyy	11​
d/mm/yyyy	10​
dd/m/yyyy	10​

So If(len(L2002)>20,2,1) needs changing to a if(find(" ",L2002.......something i.e. find the space after the d/m/y bit to work out the length, if it's 9 take 1 digit, if it's 11 take 2 digits. But if it's 10 length then that gets complicated going to need to embed AND, within embedded If's, must be an easier way. Tried if with embedded if statements with an AND for the last part and lost track of the number of the bracket placement!

 

[ViperStripes]

Got it, replace the len section with:
if(find(" ",L2002)=9,1,if(find(" ",L2002)=11,2,if(find("/",L2002)=2,2,if(find("/",L2002)=3,1))))

That gives an overall formula of:
DATE(MID(L2002,FIND(" ",L2002)-4,4),LEFT(L2002,FIND("/",L2002)-1),MID(L2002,FIND("/",L2002)+1,IF(FIND(" ",L2002)=9,1,IF(FIND(" ",L2002)=11,2,IF(FIND("/",L2002)=2,2,IF(FIND("/",L2002)=3,1))))))+MID(L2002,FIND(" ",L2002,1)+1,99)

Thanks all, especially Dave Potton

 

[Rick Rothstein]

First, definitely import that column as Text. Once you have done that, here is the formula I came up with...

=TEXT(DATE(MID(L2002,FIND(" ",L2002)-4,4),SUBSTITUTE(MID(L2002,FIND("/",L2002)+1,2),"/",""),LEFT(L2002,FIND("/",L2002)-1)),"dd/mm/yyyy")&" "&TEXT(MID(L2002,FIND(" ",L2002)+1,11),"h:mm")

 

[Dave Patton]

3 more that seem to work
Please check on your actual data

Date and Time.xlsm
	A	B	C	D
42				With Let available in 365
43	8/6/2021 12:18:50 PM	6-Aug-2021 12:18 PM	6-Aug-2021 12:18 PM	6-Aug-2021 12:18 PM
44	10/2/2020 10:45:33 AM	2-Oct-2020 10:45 AM	2-Oct-2020 10:45 AM	2-Oct-2020 10:45 AM
45	10/18/2020 11:09:04 PM	18-Oct-2020 11:09 PM	18-Oct-2020 11:09 PM	18-Oct-2020 11:09 PM
9e

Cell Formulas
Range	Formula
B43:B45	B43	=DATE(MID(A43,FIND(" ",A43)-4,4),LEFT(A43,FIND("/",A43)-1),MID(A43,FIND("/",A43)+1,IF(OR(LEN(A43)={20,21}),1,2)))+MID(A43,FIND(" ",A43)+1,99)
C43:C45	C43	=DATE(MID(A43,FIND(" ",A43)-4,4),LEFT(A43,FIND("/",A43)-1),MID(A43,FIND("/",A43)+1,IF(OR(FIND(" ",A43)={9,10}),1,2)))+MID(A43,FIND(" ",A43)+1,99)
D43:D45	D43	=LET(s,FIND(" ",A43),ss,FIND("/",A43),DATE(MID(A43,s-4,4),LEFT(A43,ss-1),MID(A43,ss+1,IF(OR(s={9,10}),1,2)))+MID(A43,s+1,99))