How to transform a date thats yy-mm-dd into yyyy-mm-dd?

[drew_darke]

The issue that occurs is when the date format in the dataset is in a 6 char date format, example: July 4th, 2020 is noted as 20-07-04.

This throws errors when trying to straight up convert the column to a date type. And when creating a custom column to format the date to yyyy-mm-dd we get the following problem:

July 4th, 2020 (noted as 20-07-04 in the data) will return 2020-07-04, great.

However, July 4th, 1996 (noted as 96-07-04 in the data) will return 2096-07-04 in the new custom column.

For the more visual people, here is a screenshot

Obviously the AI is just finding the pattern of "Oh looks like I'll just concat 20 to beginning of the year" but this doesnt work.

(And unfortunetely starting the custom column on the fields with 19XX does not fix the problem, it actually just appends 19 to everything instead)

Anybody got a trick to work around this?

[v-kkf-msft · Accepted solution]

Hi @drew_darke ,

Try add a custom column:

=#date(
    Number.FromText(
        if Text.Start([date],1) >= "3" 
          then "19" & Text.Start([date],2) 
          else "20" & Text.Start([date],2)
    ),
    Number.FromText(Text.Range([date],3,2)),
    Number.FromText(Text.End([date],2))
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

PQ is intelligent enough beyond expectation,