How convert Text to Date & Time

[shlomek12]

My text is the following format :

20/4/2021 16:46

20/4/2021 09:40

20/4/2021 07:52

20/4/2021 16:36

20/4/2021 14:25

20/4/2021 14:28

20/4/2021 14:31

20/4/2021 17:18

The middle number "4" is the Month

I need to covert this as Dates (MM/DD/YYYY) and Time (06:46 AM New York Time Zone)

 

[Dave Patton]

Try Data Data Text to Column date option dmy
Format to your preference

Date and Time 2021.xlsm
	A	B	C	D
1
2	20-04-21 16:46	Apr 20, 2021	16:46:00	Apr 20, 2021 16:46
3	20-04-21 09:40	Apr 20, 2021	09:40:00	Apr 20, 2021 09:40
4	20-04-21 07:52	Apr 20, 2021	07:52:00	Apr 20, 2021 07:52
5	20-04-21 16:36	Apr 20, 2021	16:36:00	Apr 20, 2021 16:36
6	20-04-21 14:25	Apr 20, 2021	14:25:00	Apr 20, 2021 14:25
7	20-04-21 14:28	Apr 20, 2021	14:28:00	Apr 20, 2021 14:28
8	20-04-21 14:31	Apr 20, 2021	14:31:00	Apr 20, 2021 14:31
9	20-04-21 17:18	Apr 20, 2021	17:18:00	Apr 20, 2021 17:18
8d

Cell Formulas
Range	Formula
D2:D9	D2	=B2+C2

 

[shlomek12]

Is there a possibility to make it in a Formula?

 

[Dave Patton]

What did you try?

 

[shlomek12]

First, Thanks for your quick response

I did not tried since I want to do it in a Formula

Can it be done in a Formula?

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C	D	E
2	20/4/2021 16:46	Apr 20, 2021	16:46:00	Apr 20, 2021 16:46	Apr 20, 2021 16:46
8d

Cell Formulas
Range	Formula
D2	D2	=B2+C2
E2	E2	=DATE(MID(A2,6,4),MID(A2,4,1),LEFT(A2,2))+RIGHT(A2,5)

Edit the time to your requirement.

 

[jtakw]

Hi,

See my first 4 row samples, for single/double digit month or days:

Book3.xlsx
	A	B
1	20/4/2021 16:46	04/20/2021 04:46 PM
2	20/12/2021 09:40	12/20/2021 09:40 AM
3	2/12/2021 07:52	12/02/2021 07:52 AM
4	20/12/2021 16:36	12/20/2021 04:36 PM
5	20/4/2021 14:25	04/20/2021 02:25 PM
6	20/4/2021 14:28	04/20/2021 02:28 PM
7	20/4/2021 14:31	04/20/2021 02:31 PM
8	20/4/2021 17:18	04/20/2021 05:18 PM
Sheet927

Cell Formulas
Range	Formula
B1:B8	B1	=MID(SUBSTITUTE(A1,"/","/"&LEFT(A1,FIND("/",A1)),2),FIND("/",A1)+1,99)+0

 

[shlomek12]

What is the Formula in B2 and in C2?

 

[shlomek12]

Hi,

See my first 4 row samples, for single/double digit month or days:

Book3.xlsx
	A	B
1	20/4/2021 16:46	04/20/2021 04:46 PM
2	20/12/2021 09:40	12/20/2021 09:40 AM
3	2/12/2021 07:52	12/02/2021 07:52 AM
4	20/12/2021 16:36	12/20/2021 04:36 PM
5	20/4/2021 14:25	04/20/2021 02:25 PM
6	20/4/2021 14:28	04/20/2021 02:28 PM
7	20/4/2021 14:31	04/20/2021 02:31 PM
8	20/4/2021 17:18	04/20/2021 05:18 PM
Sheet927

Cell Formulas
Range	Formula
B1:B8	B1	=MID(SUBSTITUTE(A1,"/","/"&LEFT(A1,FIND("/",A1)),2),FIND("/",A1)+1,99)+0

Thank you very much

 

[jtakw]

You're welcome, thanks for the feedback.