Extract Date and Time

[kmonkmol]

Hello
I have a Sales Data Sheet, I need Extract my date and time in separate column from a text range. i attached my file please reply me.

Here is the link

Data.xlsx

Shared with Dropbox

www.dropbox.com

 

[awoohaw]

Here is the corrected version:

mr excel questions 21.xlsm
	B									K	L
15	3/31/2023 12:42:49 AM									2023-03-31	12:42:49 AM
16	3/31/2023 12:44:39 AM									2023-03-31	12:44:39 AM
17	3/31/2023 12:45:34 AM									2023-03-31	12:45:34 AM
18	3/31/2023 12:46:35 AM									2023-03-31	12:46:35 AM
19	3/31/2023 12:47:02 AM									2023-03-31	12:47:02 AM
20	3/31/2023 12:47:50 AM									2023-03-31	12:47:50 AM
21	3/31/2023 9:13:39 PM									2023-03-31	09:13:39 PM
kmonkmol

Cell Formulas
Range	Formula
K15:K21	K15	=DATE(RIGHT(LEFT(B15,FIND(" ",B15)-1),4),LEFT(B15,FIND("/",B15)-1),MID(B15,FIND("/",B15)+1,FIND("/",B15,FIND("/",B15)+1)-(FIND("/",B15)+1)))
L15:L21	L15	=TIMEVALUE(MID(B15,FIND(" ",B15)+1,FIND(":",B15)-(FIND(" ",B15)+1))&":"&MID(B15,FIND(":",B15)+1,FIND(":",B15,FIND(":",B15)+1)-(FIND(":",B15)+1))&":"&LEFT(RIGHT(B15,5),2)& " " & RIGHT(B15,2))

 

[kmonkmol]

Here is the corrected version:

mr excel questions 21.xlsm
	B									K	L
15	3/31/2023 12:42:49 AM									2023-03-31	12:42:49 AM
16	3/31/2023 12:44:39 AM									2023-03-31	12:44:39 AM
17	3/31/2023 12:45:34 AM									2023-03-31	12:45:34 AM
18	3/31/2023 12:46:35 AM									2023-03-31	12:46:35 AM
19	3/31/2023 12:47:02 AM									2023-03-31	12:47:02 AM
20	3/31/2023 12:47:50 AM									2023-03-31	12:47:50 AM
21	3/31/2023 9:13:39 PM									2023-03-31	09:13:39 PM
kmonkmol

Cell Formulas
Range	Formula
K15:K21	K15	=DATE(RIGHT(LEFT(B15,FIND(" ",B15)-1),4),LEFT(B15,FIND("/",B15)-1),MID(B15,FIND("/",B15)+1,FIND("/",B15,FIND("/",B15)+1)-(FIND("/",B15)+1)))
L15:L21	L15	=TIMEVALUE(MID(B15,FIND(" ",B15)+1,FIND(":",B15)-(FIND(" ",B15)+1))&":"&MID(B15,FIND(":",B15)+1,FIND(":",B15,FIND(":",B15)+1)-(FIND(":",B15)+1))&":"&LEFT(RIGHT(B15,5),2)& " " & RIGHT(B15,2))

thank you

 

[awoohaw]

Happy to help you find a solution.

Another suggestion, is to please update your profile here with the version of excel that you use. That way, with future questions you can get faster and appropriate answers to your questions.

Best Wishes!