Forum Discussion
Formatting date with inconsistent input data in same column
Hi all, I'm having trouble formatting a column of date data which has two date formats from the raw data. The input data has a mixture of d/m/yyyy and dd/mm/yyyy For example there is 2/8/2...
There is the option for dd-mmm-yyyy and mm/dd/yy but there is not option for dd/mm/yyyy.
I.e. there is not option to have full number date format with day first.
To add some clarity to the issue in the image below I've duplicated the column, leaving the first column in text/num format and second in date format (mm/dd/yy) using the modeling ribbon rather than in the query editor.
As you can see it recognoises the ordering on month and day changes depending on whether the original column uses 8 or 08 for the month value.
.
Also this is what the data looks like in Excel. You see some recognised dates and some as text