Identifying Dates

[Mark1234]

Hi All,

Wondering if anyone can help me with the error. I am trying to write a if statement that will output the what is already shown, while the fixing the european formatted date. Pleae see below for the data set, the outcome, and the function. I must be missing something, if anyone can help that would be awesome.

 

Thanks in advance.

[Vijay_A_Verma]

Insert ths statement

= Table.AddColumn(Source, "Custom", each if Text.Contains([DateStarted],"/") then Date.From([DateStarted]) else if Text.Contains([DateStarted],"-") then #date(Number.From(Text.Split([DateStarted],"-"){2}),Number.From(Text.Split([DateStarted],"-"){1}),Number.From(Text.Split([DateStarted],"-"){0}))
else null, type date)

[Mark1234]

Thanks, but the statement didn't work for me.

[HotChilli · Accepted solution]

The 'Any' datatype isn't a particularly useful one if you can avoid it.

I suspect there's an implicit conversion to date going on for some of the values that have double figures for day but it can't convert the single figure days.

If you can get the column in text format first (quite early in the Steps) (and maybe pad the start for single figure days) you'll have a better chance.

I think you might have to look at the 2nd 'if' clause (it's different than the first) also.

And it might have been better if you had provided the DateStarted column and type since that's the one that's in the formula.

[Mark1234]

Thank you, adding the extra step to make the column text works. For some reason it didn't work, maybe I was doing something wrong. 

Sorry about the screen shot, I made a copy of it, so I could put it in a screen shot together.