Convert number to Date in Query Editor

[jp16]

Hi, my raw data look like 20190618 and query editor recongnize it as Number (with the 123 icon). However, when i change type to date using Locale (i wan dd/mm/yyyy). It show error. Anyone can advice how?

[FDibbins]

If that is indeed how your data looks in the formula bar, that is not a date, it is just some random numbers, as far as excel is concerned.

If you intended to get this as the date...
6/18/2019

Then you need to use this formula...
=DATE(LEFT(U11,4),MID(U11,5,2),RIGHT(U11,2))
Assuming the cell is U11. Adjust as needed.

If that was a real date, it would show as 6/18/2019 (or, if formatted as General/Number...43634

[Bo_Ry]

1. Change type to Text.
2. Then change to Date by add new step

[alansidman]

Here is Mcode if you are doing this in Power Query

Please Login or Register  to view this content.

You need to convert the "number value" to text and then apply the Date.FromText function in a custom column.