convert to a valid date and time

[ExcelNewbie2020]

I have these date and time formats. I tried different date and time function but are not working. How can I convert these to become a valid date and time format (mm-dd-yyyy hh:mm:ss) ..many thanks

10-28-2021 | 08:57:12​

10-28-2021 | 08:51:14​

10-28-2021 | 08:51:14​

10-28-2021 | 08:41:34​

10-28-2021 | 08:41:34​

10-28-2021 | 08:22:55​

10-28-2021 | 08:22:55​

10-28-2021 | 08:21:34​

10-28-2021 | 08:21:34​

10-28-2021 | 08:20:43​

10-28-2021 | 08:20:43​

10-28-2021 | 08:20:28​

10-28-2021 | 08:20:28​

10-28-2021 | 08:10:04​

10-28-2021 | 08:10:04​

10-28-2021 | 08:07:55​

 

[anand3dinesh]

if your values in column A then try this in column B
=TRIM(REPLACE(A1,12,1,""))

 

[ExcelNewbie2020]

if your values in column A then try this in column B
=TRIM(REPLACE(A1,12,1,""))

thank you.. the formula only remove the line in the middle. It did not converted into a valid date and time..

 

[Peter_SSs]

Is your local date format d/m/y or m/d/y?

 

[ExcelNewbie2020]

Is your local date format d/m/y or m/d/y?

its mdy

 

[Peter_SSs]

its mdy

Mine is dmy so I cannot easily test this with your exact sample data, but try

=SUBSTITUTE(A1,"|","")+0

 

[ExcelNewbie2020]

Mine is dmy so I cannot easily test this with your exact sample data, but try

=SUBSTITUTE(A1,"|","")+0

thanks, but it returns #VALUE!

 

[kvsrinivasamurthy]

Data in A2. In B2

=DATE(MID(A2,7,4), LEFT(A2,2),MID(A2,4,2))+VALUE(MID(A2,FIND("| ",A2)+2,8))

Format the cell B2 for dd/mm/yyyy hh:mm:ss
Date and month should have 2 digits . Otherwise we have to change formula.
Eg For Sep it should be 09

 

[anand3dinesh]

try this
=CONCATENATE(MID(A1,4,2),"/",LEFT(A1,2),"/",MID(A1,7,4)," ",MID(A1,14,8))+0

 

[vladimiratanasiu]

I have these date and time formats. I tried different date and time function but are not working. How can I convert these to become a valid date and time format (mm-dd-yyyy hh:mm:ss) ..many thanks

10-28-2021 | 08:57:12​

10-28-2021 | 08:51:14​

10-28-2021 | 08:51:14​

10-28-2021 | 08:41:34​

10-28-2021 | 08:41:34​

10-28-2021 | 08:22:55​

10-28-2021 | 08:22:55​

10-28-2021 | 08:21:34​

10-28-2021 | 08:21:34​

10-28-2021 | 08:20:43​

10-28-2021 | 08:20:43​

10-28-2021 | 08:20:28​

10-28-2021 | 08:20:28​

10-28-2021 | 08:10:04​

10-28-2021 | 08:10:04​

10-28-2021 | 08:07:55​

Try this :

Book1
	A	B
1	10-28-2021 | 08:57:12	10/28/2021 08:57:12
2	10-28-2021 | 08:51:14	10/28/2021 08:51:14
3	10-28-2021 | 08:51:14	10/28/2021 08:51:14
4	10-28-2021 | 08:41:34	10/28/2021 08:41:34
5	10-28-2021 | 08:41:34	10/28/2021 08:41:34
6	10-28-2021 | 08:22:55	10/28/2021 08:22:55
7	10-28-2021 | 08:22:55	10/28/2021 08:22:55
8	10-28-2021 | 08:21:34	10/28/2021 08:21:34
9	10-28-2021 | 08:21:34	10/28/2021 08:21:34
10	10-28-2021 | 08:20:43	10/28/2021 08:20:43
11	10-28-2021 | 08:20:43	10/28/2021 08:20:43
12	10-28-2021 | 08:20:28	10/28/2021 08:20:28
13	10-28-2021 | 08:20:28	10/28/2021 08:20:28
14	10-28-2021 | 08:10:04	10/28/2021 08:10:04
15	10-28-2021 | 08:10:04	10/28/2021 08:10:04
16	10-28-2021 | 08:07:55	10/28/2021 08:07:55
Sheet1

Cell Formulas
Range	Formula
B1:B16	B1	=CONCATENATE(MID(A1,4,2),"/",LEFT(A1,2),"/",MID(A1,7,4)," ",MID(A1,14,8))+0