inconsistent reading of date format UK<>US

[Joel_Clark]

I am reading in data from an online spreadsheet in SharePoint and Power Query is inconsistently reading the date formats. I've double checked the source file and can see no reason for it as the values look consistently correct. 

On the left is the Power Query view, data type is date and UK formats hold until 31st of January at which point is seems to switch to US format and shows the second of March instead of 3rd Feb. You can see the source data in the other image which i've checked as clean. The real problem for me is the inconsistency. If it was one way or the other i could handle it.

Any ideas, hint, tips or support are gratefully received!

Thanks

[Joel_Clark]

Seems it didn't post the images first time round

[Jimmy801 · Accepted solution]

Hello @Joel_Clark 

There seems to be something utterly wrong in your database because I'm not able to undestand what dataformat you have in Excel

the first lines are indicating 16th Jannuary - meaning MM/DD/YYYY. But this logic can not be applied to the other lines. I suppose this dates are mainainted as text in your Excel. 

SO just change the date format in Excel and you should be fine

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy