Extract Date and Time

kmonkmol · Apr 1, 2023

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 · Apr 1, 2023

what version of excel do you use?

smozgur · Apr 1, 2023

Your Date column contains a date value, not a text value. In this case:

Book1

B

C

D

E

1

Date

EDate

ETime

2

3/31/2023 12:02:40 AM

3/31/2023

12:02 AM

3

3/31/2023 12:04:51 AM

3/31/2023

12:04 AM

4

3/31/2023 12:05:40 AM

3/31/2023

12:05 AM

5

3/31/2023 12:08:37 AM

3/31/2023

12:08 AM

6

3/31/2023 12:09:20 AM

3/31/2023

12:09 AM

7

3/31/2023 12:12:33 AM

3/31/2023

12:12 AM

8

3/31/2023 12:18:35 AM

3/31/2023

12:18 AM

Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=DATE(YEAR(B2),MONTH(B2),DAY(B2))
E2:E8	E2	=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

Note: You need to format the new date and time columns accordingly.

awoohaw · Apr 1, 2023

For 365:

mr excel questions 21.xlsm

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

312,556

3/31/2023 12:02:40 AM

Mobile Sales 1

MS1J

MEIT15150

35424

PRODUCT-1

1

50

2023-03-31

12:02:40 AM

3

312,557

3/31/2023 12:04:51 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-2

2

5

10

2023-03-31

12:04:51 AM

4

312,558

3/31/2023 12:05:40 AM

Mobile Sales 1

MS1J

MEIT15150

8985

PRODUCT-3

1

25

2023-03-31

12:05:40 AM

5

312,559

3/31/2023 12:08:37 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-2

1

5

2023-03-31

12:08:37 AM

6

312,560

3/31/2023 12:09:20 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-2

1

5

2023-03-31

12:09:20 AM

7

312,561

3/31/2023 12:12:33 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-2

2

5

10

2023-03-31

12:12:33 AM

kmonkmol

Cell Formulas
Range	Formula
K2:K7	K2	=DATE(RIGHT(TEXTBEFORE(B2," "),4),TEXTBEFORE(B2,"/"),TEXTAFTER(TEXTBEFORE(B2,"/",2),"/"))
L2:L7	L2	=TIMEVALUE(TEXTAFTER(B2," "))

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 · Apr 1, 2023

Here is another option if column B are datevalues:

mr excel questions 21.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

8

312,562

03/31/2023 12:18:35 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-4

1

25

2023-03-31

12:18:35 AM

9

312,563

03/31/2023 12:20:29 AM

Mobile Sales 1

MS1J

MEIT15150

0351

PRODUCT-5

1

25

2023-03-31

12:20:29 AM

10

312,564

03/31/2023 12:21:18 AM

Mobile Sales 1

MS1J

MEIT15150

8985

PRODUCT-6

1

95

2023-03-31

12:21:18 AM

11

312,565

03/31/2023 12:26:06 AM

Mobile Sales 1

MS1J

MEIT15150

38192

PRODUCT-7

1

25

2023-03-31

12:26:06 AM

12

312,566

03/31/2023 12:33:36 AM

Mobile Sales 1

MS1J

MEIT15150

28431

PRODUCT-8

1

15

2023-03-31

12:33:36 AM

13

312,567

03/31/2023 12:37:12 AM

Mobile Sales 1

MS1J

MEIT15150

19351

PRODUCT-9

1

30

2023-03-31

12:37:12 AM

14

312,568

03/31/2023 12:40:00 AM

Mobile Sales 1

MS1J

MEIT15150

7963

PRODUCT-4

1

25

2023-03-31

12:40:00 AM

kmonkmol

Cell Formulas
Range	Formula
K8:K14	K8	=INT(B8)
L8:L14	L8	=MOD(B8,1)

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

kmonkmol · Apr 1, 2023

getting Value error "#VALUE!

awoohaw · Apr 1, 2023

@kmonkmoi which post solution is getting the #VALUE Error.
It is IMPORTANT to know what version of excel you are using.

kmonkmol · Apr 1, 2023

awoohaw said:
@kmonkmoi which post solution is getting the #VALUE Error.
It is IMPORTANT to know what version of excel you are using.

awoohaw said:
what version of excel do you use?

Microsoft Office Professional Plus 2019

awoohaw · Apr 1, 2023

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

kmonkmol · Apr 1, 2023

kmonkmol said:
getting Value error "#VALUE!

awoohaw said:
Here is another option if column B are datevalues:

mr excel questions 21.xlsm
A B C D E F G H I J K L
8 312,562 03/31/2023 12:18:35 AM Mobile Sales 1 MS1J MEIT15150 7963 PRODUCT-4 1 25 25 2023-03-31 12:18:35 AM
9 312,563 03/31/2023 12:20:29 AM Mobile Sales 1 MS1J MEIT15150 0351 PRODUCT-5 1 25 25 2023-03-31 12:20:29 AM
10 312,564 03/31/2023 12:21:18 AM Mobile Sales 1 MS1J MEIT15150 8985 PRODUCT-6 1 95 95 2023-03-31 12:21:18 AM
11 312,565 03/31/2023 12:26:06 AM Mobile Sales 1 MS1J MEIT15150 38192 PRODUCT-7 1 25 25 2023-03-31 12:26:06 AM
12 312,566 03/31/2023 12:33:36 AM Mobile Sales 1 MS1J MEIT15150 28431 PRODUCT-8 1 15 15 2023-03-31 12:33:36 AM
13 312,567 03/31/2023 12:37:12 AM Mobile Sales 1 MS1J MEIT15150 19351 PRODUCT-9 1 30 30 2023-03-31 12:37:12 AM
14 312,568 03/31/2023 12:40:00 AM Mobile Sales 1 MS1J MEIT15150 7963 PRODUCT-4 1 25 25 2023-03-31 12:40:00 AM
kmonkmol
Cell Formulas
Range Formula
K8:K14 K8 =INT(B8)
L8:L14 L8 =MOD(B8,1)
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

Value Error

Extract Date and Time

kmonkmol

Board Regular

Data.xlsx

Excel Facts

awoohaw

Well-known Member

smozgur

BatCoder

awoohaw

Well-known Member

awoohaw

Well-known Member

kmonkmol

Board Regular

awoohaw

Well-known Member

kmonkmol

Board Regular

awoohaw

Well-known Member

kmonkmol

Board Regular

Similar threads

Forum statistics

Share this page

Extract Date and Time

Board Regular

Excel Facts

Well-known Member

BatCoder

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock