date format issue - not corectly in all rows

[Mareno_123]

hi,

i have date format column START (d. m. yyyy h:nn:ss)  and date format column END(d. m. yyyy h:nn:ss). I wanna compare them with exact but not in format (d. m. yyyy h:nn:ss) but only in  (dd/mm/yyyy).

In power query is data type : Date/TIME

I used functions

date = FORMAT(table[start];"DD-MM-YYYY")

date end = FORMAT(table[end];"DD-MM-YYYY")

date exact = EXACT(table[date];ld_dzv[date end]) .

And problem ? So many dates give me wrong position date and month like:

7.1.2020 (7. january.2020) return 1.7.2020 (1.july.2020) but

13.1.2020 (13. january.2020) return 13.1.2020 (13.january.2020)

I dont know where i m wrong

in the photo (red are wrong , green are OK)

[luapdoniv]

After converting to only "Date" format, do some modeling steps on that column to split that by delimiter, splitting out the day, month, year and then concatening them in the required format as to dd/mm/yyyy or mm/dd/yyyy and then format the resultant column to date.

If you can share the table, can check and resolve for you.

[Mareno_123]

for me works : 

ok Start = FORMAT(table[start].[Date];"YYYYMMDD")  

[Mariusz]

Hi @Mareno_123 

Check if your Regional settings are correct as most of the time this is the case with date formating issues.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn