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

 

[AhoyNC]

Another option. You will probably need to format cells as Date and Time.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order ID	Date	Oulet	User	Terminal	SKU	Product	Quantity	Unit Price	Total	EDate	ETime
2	312556	3/31/2023 0:02	Mobile Sales 1	MS1J	MEIT15150	35424	PRODUCT-1	1	50	50	3/31/2023	12:02 AM
3	312557	3/31/2023 0:04	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-2	2	5	10	3/31/2023	12:04 AM
4	312558	3/31/2023 0:05	Mobile Sales 1	MS1J	MEIT15150	8985	PRODUCT-3	1	25	25	3/31/2023	12:05 AM
5	312559	3/31/2023 0:08	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-2	1	5	5	3/31/2023	12:08 AM
6	312560	3/31/2023 0:09	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-2	1	5	5	3/31/2023	12:09 AM
7	312561	3/31/2023 0:12	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-2	2	5	10	3/31/2023	12:12 AM
Sheet1

Cell Formulas
Range	Formula
K2:K7	K2	=INT(B2)
L2:L7	L2	=B2-INT(B2)

 

[kmonkmol]

Post #5 will work for your if COLUMN B are DATE/TIME Values.

Post #5 also Value Error

 

[awoohaw]

Please confirm your regional settings for date and time.

Here is a universal excel version:

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
16	312,570	3/31/2023 12:44:39 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:44:39 PM
17	312,571	3/31/2023 12:45:34 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:45:34 PM
18	312,572	3/31/2023 12:46:35 AM	Mobile Sales 1	MS1J	MEIT15150	7671	PRODUCT-11	2	30	60	2023-03-31	12:46:35 PM
19	312,573	3/31/2023 12:47:02 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:47:02 PM
kmonkmol

Cell Formulas
Range	Formula
K16:K19	K16	=DATE(RIGHT(LEFT(B16,FIND(" ",B16)-1),4),LEFT(B16,FIND("/",B16)-1),MID(B16,FIND("/",B16)+1,FIND("/",B16,FIND("/",B16)+1)-(FIND("/",B16)+1)))
L16:L19	L16	=TIME(MID(B16,FIND(" ",B16)+1,FIND(":",B16)-(FIND(" ",B16)+1)),MID(B16,FIND(":",B16)+1,FIND(":",B16,FIND(":",B16)+1)-(FIND(":",B16)+1)),LEFT(RIGHT(B16,5),2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1317	Expression	=MOD(A1,1)=0	text	NO
H1:H1317	Expression	=MOD(A1,1)=0	text	NO
I1:I1317	Expression	=MOD(A1,1)=0	text	NO
J1:J1317	Expression	=MOD(A1,1)=0	text	NO

 

[awoohaw]

You have received many different approaches that will work, most of them in any version of excel.
You will need to post a sample of your worksheet.
If you are editing the formulas then you are probably editing it incorrectly.

 

[kmonkmol]

Please confirm your regional settings for date and time.

Here is a universal excel version:

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
16	312,570	3/31/2023 12:44:39 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:44:39 PM
17	312,571	3/31/2023 12:45:34 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:45:34 PM
18	312,572	3/31/2023 12:46:35 AM	Mobile Sales 1	MS1J	MEIT15150	7671	PRODUCT-11	2	30	60	2023-03-31	12:46:35 PM
19	312,573	3/31/2023 12:47:02 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:47:02 PM
kmonkmol

Cell Formulas
Range	Formula
K16:K19	K16	=DATE(RIGHT(LEFT(B16,FIND(" ",B16)-1),4),LEFT(B16,FIND("/",B16)-1),MID(B16,FIND("/",B16)+1,FIND("/",B16,FIND("/",B16)+1)-(FIND("/",B16)+1)))
L16:L19	L16	=TIME(MID(B16,FIND(" ",B16)+1,FIND(":",B16)-(FIND(" ",B16)+1)),MID(B16,FIND(":",B16)+1,FIND(":",B16,FIND(":",B16)+1)-(FIND(":",B16)+1)),LEFT(RIGHT(B16,5),2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1317	Expression	=MOD(A1,1)=0	text	NO
H1:H1317	Expression	=MOD(A1,1)=0	text	NO
I1:I1317	Expression	=MOD(A1,1)=0	text	NO
J1:J1317	Expression	=MOD(A1,1)=0	text	NO

Dear @awoohaw
I truly appreciate your thorough and insightful response - it was a great answer to my question.

 

[awoohaw]

@kmonkmol , My pleasure. I am happy you found a solution.
As you can see when you have dates embedded in text or just as text they are super hard to work with. If at all possible you can avoid that in other workbooks you will save yourself a lot of headaches!

Best wishes!

 

[kmonkmol]

Please confirm your regional settings for date and time.

Here is a universal excel version:

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
16	312,570	3/31/2023 12:44:39 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:44:39 PM
17	312,571	3/31/2023 12:45:34 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:45:34 PM
18	312,572	3/31/2023 12:46:35 AM	Mobile Sales 1	MS1J	MEIT15150	7671	PRODUCT-11	2	30	60	2023-03-31	12:46:35 PM
19	312,573	3/31/2023 12:47:02 AM	Mobile Sales 1	MS1J	MEIT15150	7963	PRODUCT-4	1	25	25	2023-03-31	12:47:02 PM
kmonkmol

Cell Formulas
Range	Formula
K16:K19	K16	=DATE(RIGHT(LEFT(B16,FIND(" ",B16)-1),4),LEFT(B16,FIND("/",B16)-1),MID(B16,FIND("/",B16)+1,FIND("/",B16,FIND("/",B16)+1)-(FIND("/",B16)+1)))
L16:L19	L16	=TIME(MID(B16,FIND(" ",B16)+1,FIND(":",B16)-(FIND(" ",B16)+1)),MID(B16,FIND(":",B16)+1,FIND(":",B16,FIND(":",B16)+1)-(FIND(":",B16)+1)),LEFT(RIGHT(B16,5),2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1317	Expression	=MOD(A1,1)=0	text	NO
H1:H1317	Expression	=MOD(A1,1)=0	text	NO
I1:I1317	Expression	=MOD(A1,1)=0	text	NO
J1:J1317	Expression	=MOD(A1,1)=0	text	NO

Why i am getting instead of "AM" getting "PM"

 

[awoohaw]

I'm not sure, what ROW is this happening in? Check to ensure the formulas reference the same ROW.
If on ROW 16, then all the cell references should say B16, not B3 or anything else.

 

[kmonkmol]

I'm not sure, what ROW is this happening in? Check to ensure the formulas reference the same ROW.
If on ROW 16, then all the cell references should say B16, not B3 or anything else.

See your example in Post #13 there is also "PM" instead of "AM"

 

[awoohaw]

here is a corrected version: