Best way to convert date and timestamp format date into a date format?

[Zee996]

I have 1000s of raw data that I have pulled from SAP that came in text format.

I am trying to convert the date and timestamp to only the date so I can summarize the monthly data through the pivot table but as the date is in text pivot is not working.

I have tried "text to column" but no luck with that.

Is there a way to clean it?

6/14/2022 12:00:00 AM

6/13/2022 12:00:00 AM

6/13/2022 12:00:00 AM

6/13/2022 12:00:00 AM

06-10-2022 00:00​

06-09-2022 00:00​

06-08-2022 00:00​

06-08-2022 00:00​

06-07-2022 00:00​

06-07-2022 00:00​

 

[alansidman]

Can you repost the data using XL2BB as currently presented the data cannot be determined as to formatting and it cannot be manipulated.

 

[Zee996]

Book4
	A
1	Transaction Date
2	6/30/2022 12:00:00 AM
3	6/29/2022 12:00:00 AM
4	6/29/2022 12:00:00 AM
5	6/29/2022 12:00:00 AM
6	6/29/2022 12:00:00 AM
7	6/29/2022 12:00:00 AM
8	6/29/2022 12:00:00 AM
9	6/29/2022 12:00:00 AM
10	6/29/2022 12:00:00 AM
11	6/29/2022 12:00:00 AM
12	6/29/2022 12:00:00 AM
13	6/29/2022 12:00:00 AM
14	6/28/2022 12:00:00 AM
15	6/28/2022 12:00:00 AM
16	6/28/2022 12:00:00 AM
17	6/28/2022 12:00:00 AM
18	6/28/2022 12:00:00 AM
19	6/28/2022 12:00:00 AM
20	6/28/2022 12:00:00 AM
21	6/28/2022 12:00:00 AM
22	6/28/2022 12:00:00 AM
23	6/28/2022 12:00:00 AM
24	6/28/2022 12:00:00 AM
25	6/28/2022 12:00:00 AM
26	6/28/2022 12:00:00 AM
27	6/28/2022 12:00:00 AM
28	6/28/2022 12:00:00 AM
29	6/28/2022 12:00:00 AM
30	6/28/2022 12:00:00 AM
31	6/28/2022 12:00:00 AM
32	6/28/2022 12:00:00 AM
33	6/28/2022 12:00:00 AM
34	6/28/2022 12:00:00 AM
35	6/27/2022 12:00:00 AM
36	6/27/2022 12:00:00 AM
37	6/27/2022 12:00:00 AM
38	6/27/2022 12:00:00 AM
39	6/27/2022 12:00:00 AM
40	6/27/2022 12:00:00 AM
41	6/26/2022 12:00:00 AM
42	6/26/2022 12:00:00 AM
43	6/23/2022 12:00:00 AM
44	6/23/2022 12:00:00 AM
45	6/23/2022 12:00:00 AM
46	6/23/2022 12:00:00 AM
47	6/22/2022 12:00:00 AM
48	6/22/2022 12:00:00 AM
49	6/22/2022 12:00:00 AM
50	6/22/2022 12:00:00 AM
51	6/22/2022 12:00:00 AM
52	6/21/2022 12:00:00 AM
53	6/21/2022 12:00:00 AM
54	6/21/2022 12:00:00 AM
55	6/21/2022 12:00:00 AM
56	6/21/2022 12:00:00 AM
57	6/19/2022 12:00:00 AM
58	6/19/2022 12:00:00 AM
59	6/19/2022 12:00:00 AM
60	6/19/2022 12:00:00 AM
61	6/19/2022 12:00:00 AM
62	6/19/2022 12:00:00 AM
63	6/19/2022 12:00:00 AM
64	6/19/2022 12:00:00 AM
65	6/19/2022 12:00:00 AM
66	6/14/2022 12:00:00 AM
67	6/14/2022 12:00:00 AM
68	6/13/2022 12:00:00 AM
69	6/13/2022 12:00:00 AM
70	6/13/2022 12:00:00 AM
71	06-10-2022 00:00
72	06-09-2022 00:00
73	06-08-2022 00:00
74	06-08-2022 00:00
75	06-07-2022 00:00
76	06-07-2022 00:00
77	06-07-2022 00:00
78	06-07-2022 00:00
79	06-07-2022 00:00
80	06-06-2022 00:00
81	06-06-2022 00:00
82	06-05-2022 00:00
83	06-05-2022 00:00
84	06-05-2022 00:00
85	06-03-2022 00:00
86	06-03-2022 00:00
87	06-03-2022 00:00
88	06-03-2022 00:00
89	06-03-2022 00:00
90	06-02-2022 00:00
91	06-02-2022 00:00
92	06-02-2022 00:00
93	06-02-2022 00:00
94	06-01-2022 00:00
95	06-01-2022 00:00
96	06-01-2022 00:00
Sheet1

 

[alansidman]

I used power query to reformat your data. Here is the Mcode to achieve that. Since you are using 365, Power Query is called Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}})
in
    #"Changed Type"

Book8
	A	B	C
1	Transaction Date		Transaction Date
2	6/30/2022 0:00		6/30/2022
3	6/29/2022 0:00		6/29/2022
4	6/29/2022 0:00		6/29/2022
5	6/29/2022 0:00		6/29/2022
6	6/29/2022 0:00		6/29/2022
7	6/29/2022 0:00		6/29/2022
8	6/29/2022 0:00		6/29/2022
9	6/29/2022 0:00		6/29/2022
10	6/29/2022 0:00		6/29/2022
11	6/29/2022 0:00		6/29/2022
12	6/29/2022 0:00		6/29/2022
13	6/29/2022 0:00		6/29/2022
14	6/28/2022 0:00		6/28/2022
15	6/28/2022 0:00		6/28/2022
16	6/28/2022 0:00		6/28/2022
17	6/28/2022 0:00		6/28/2022
18	6/28/2022 0:00		6/28/2022
19	6/28/2022 0:00		6/28/2022
20	6/28/2022 0:00		6/28/2022
21	6/28/2022 0:00		6/28/2022
22	6/28/2022 0:00		6/28/2022
23	6/28/2022 0:00		6/28/2022
24	6/28/2022 0:00		6/28/2022
25	6/28/2022 0:00		6/28/2022
26	6/28/2022 0:00		6/28/2022
27	6/28/2022 0:00		6/28/2022
28	6/28/2022 0:00		6/28/2022
29	6/28/2022 0:00		6/28/2022
30	6/28/2022 0:00		6/28/2022
31	6/28/2022 0:00		6/28/2022
32	6/28/2022 0:00		6/28/2022
33	6/28/2022 0:00		6/28/2022
34	6/28/2022 0:00		6/28/2022
35	6/27/2022 0:00		6/27/2022
36	6/27/2022 0:00		6/27/2022
37	6/27/2022 0:00		6/27/2022
38	6/27/2022 0:00		6/27/2022
39	6/27/2022 0:00		6/27/2022
40	6/27/2022 0:00		6/27/2022
41	6/26/2022 0:00		6/26/2022
42	6/26/2022 0:00		6/26/2022
43	6/23/2022 0:00		6/23/2022
44	6/23/2022 0:00		6/23/2022
45	6/23/2022 0:00		6/23/2022
46	6/23/2022 0:00		6/23/2022
47	6/22/2022 0:00		6/22/2022
48	6/22/2022 0:00		6/22/2022
49	6/22/2022 0:00		6/22/2022
50	6/22/2022 0:00		6/22/2022
51	6/22/2022 0:00		6/22/2022
52	6/21/2022 0:00		6/21/2022
53	6/21/2022 0:00		6/21/2022
54	6/21/2022 0:00		6/21/2022
55	6/21/2022 0:00		6/21/2022
56	6/21/2022 0:00		6/21/2022
57	6/19/2022 0:00		6/19/2022
58	6/19/2022 0:00		6/19/2022
59	6/19/2022 0:00		6/19/2022
60	6/19/2022 0:00		6/19/2022
61	6/19/2022 0:00		6/19/2022
62	6/19/2022 0:00		6/19/2022
63	6/19/2022 0:00		6/19/2022
64	6/19/2022 0:00		6/19/2022
65	6/19/2022 0:00		6/19/2022
66	6/14/2022 0:00		6/14/2022
67	6/14/2022 0:00		6/14/2022
68	6/13/2022 0:00		6/13/2022
69	6/13/2022 0:00		6/13/2022
70	6/13/2022 0:00		6/13/2022
71	10/6/2022		10/6/2022
72	9/6/2022		9/6/2022
73	8/6/2022		8/6/2022
74	8/6/2022		8/6/2022
75	7/6/2022		7/6/2022
76	7/6/2022		7/6/2022
77	7/6/2022		7/6/2022
78	7/6/2022		7/6/2022
79	7/6/2022		7/6/2022
80	6/6/2022		6/6/2022
81	6/6/2022		6/6/2022
82	5/6/2022		5/6/2022
83	5/6/2022		5/6/2022
84	5/6/2022		5/6/2022
85	3/6/2022		3/6/2022
86	3/6/2022		3/6/2022
87	3/6/2022		3/6/2022
88	3/6/2022		3/6/2022
89	3/6/2022		3/6/2022
90	2/6/2022		2/6/2022
91	2/6/2022		2/6/2022
92	2/6/2022		2/6/2022
93	2/6/2022		2/6/2022
94	1/6/2022		1/6/2022
95	1/6/2022		1/6/2022
96	1/6/2022		1/6/2022
Sheet1