Pasre date giving error

[niv_qrm]

Hi,

I am not sure what I am doing wrong, but when I try to Parse date I am getting error after 13th of the month.

I have done this before and worked previously but for some reason I am getting error.

Order date is in this format

If I usually extract the date part and do Parse date.

Please see the below. Previously this used to work. 

I tried Change Type with Locale and I am still getting the error. 

Please help! I am not sure what else to try and why I am getting this error.

Thanks a lot!

[v-stephen-msft]

Hi @niv_qrm ,

See the sample input values, they're DD/MM/YYYY, but your column from 'from example' is MM/DD/YYYY, so it will be wrong.

Local try to choose English (United Kingdom)

Best Regards,

Stephen Tao

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

[niv_qrm · Accepted solution]

Hi @v-stephen-msft,

Thanks for your reply. I figured out the mistake but forgot to come and check the replies here.

This table is a mix of data coming form different systems with different date formats.

So I seperated those ones with different format, then split the date and concatenated it and then used parse date and it fixed. For some reason when I try parse date isn't working when the data date format in US format. I tried using the Locale with UK, UAE but still I kept getting error. This is the only way I could fix it. 

Thanks a lot for the replies. 

[niv_qrm]

Just wanted to add, I also created another column " from example" and changed the date to dd-mm-yyyy and tried to parse this column. The first few columns came well, but then other dates are still showing error. 

This is the error

Thanks a lot!

[amitchandak]

@niv_qrm , because power assume date will come in your system format only. So it want date in dd/mm/yyyy format and getting date in mm/dd/yyyy format

Change you system setting to US format or treat is as text and get date in another format

MM/DD/YYYY to DD/MM/YYYY
#date( Number.FromText(Text.End(DD__MM__YY[date],4)),Number.FromText(Text.Start(DD__MM__YY[Version_Id],2)), Number.FromText(Text.Middle(DD__MM__YY[date],3,2)) )

[niv_qrm]

Hi @amitchandak 

I have been changing the format easily without much issues till today. 

I tried keeping it as text and changing the format like you mentioned but I still got error.

What interesting is I have a list of dates that are done in the correct format even for the dates after 12th of the month and then there is a bunch with errors

Again, same error 

Why does it get confused for some dates ?

Thanks and regards,

[manikumar34]

@niv_qrm , 

Click on the error and share screenshot of it, that would help on understanding the exact error

[niv_qrm]

This is the error I am getting.

Thank you!