Help with transforming a string into date / time format

2020-06-26_11-43-01.png

Basically I have a field which can either include a date/time string in

dmmyyyyhhmm or ddmmyyyyhhmm depending on day of the month

I'm trying to establish a function or formula I can use to transform that into

yyyy-mm-dd hh:mm:ss

The way the string is currently formed, Excel won't even recognise it as a date / time.

Using Power Query/Get and Transform, here is the Mcode to change your Date/Time Column

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

Excel 2016 (Windows) 32 bit

	A	B
1	Date Time	Reading
2	2011-01-1 00:00	1.329
3	2011-01-1 00:10	1.324
4	2011-01-1 00:20	1.322
5	2011-01-1 00:30	1.334
6	2011-01-1 00:40	1.35
7	2011-01-1 00:50	1.38
8	2011-01-1 01:00	1.414
9	2011-01-1 01:10	1.455
10	2011-01-1 01:20	1.504
11	2011-01-1 01:30	1.563
12	2011-01-1 01:40	1.631
13	2011-01-1 01:50	1.708
14	2011-01-1 02:00	1.795
15	2011-01-1 02:10	1.886
16	2014-11-16 16:00	1.153
17	2014-11-16 16:10	1.29
18	2014-11-16 16:20	1.425
19	2014-11-16 16:30	1.562
20	2014-11-16 16:40	1.699
21	2014-11-16 16:50	1.833
22	2014-11-16 17:00	1.954
23	2014-11-16 17:10	2.082
24	2014-11-16 17:20	2.208
25	2014-11-16 17:30	2.328
26	2014-11-16 17:40	2.447
27	2014-11-16 17:50	2.567
28	2014-11-16 18:00	2.69

Sheet: Sheet1

You can use this formula...

Formula:

Please Login or Register  to view this content.

and Custom Format is output using yyyy-mm-dd hh:mm for the Type pattern.

Try this in D2:

Please Login or Register  to view this content.

Format cell as "yy-mm-dd hh:mm"

If you don't want to format it and keep it as TEXT:

Please Login or Register  to view this content.