How to Correct Different Format Dates in a Column via DAX?

[RahimZulfiqar87]

How to Correct Different Format Dates in a Column via DAX?

Dates

6/1/2023
6/2/2023
6/3/2023
6/4/2023
6.5.2023
6.6.2023
6.7.2023
2023-27-6
27-6-2023
31052022
6..20…2023

[tamerj1]

Hi @RahimZulfiqar87 
If you cannot tell which number is the month and which number is the day, then there is no tool on earth can solve your problem. For example:

6.5.2023

2023.6.5

Who can tell if this is UK time format or US time format? Is it June 5th or May 6th? Would you take the responsibility along with the risk of producing wrong results? This need to be fixed from the source only.

[RahimZulfiqar87]

In the correction column I have format all dates in MM/DD/YYYY format. I hope this will helps you and this is US based time zone

[tamerj1]

@RahimZulfiqar87 

In (1) you are assuming "MM/DD/YYYY". However, in (2) it is "DD/MM/YYYY", and we could identified that only because the day is greater than 12. For any date where the day is less than or equal to 12 you will never be able whether it is intended to be "MM/DD/YYYY" or "DD/MM/YYYY".

Therefore, you don't know for sure that (1) is "MM/DD/YYYY", but you only assumed.

[tamerj1]

@RahimZulfiqar87 

In (1) you are assuming "MM/DD/YYYY". However, in (2) it is "DD/MM/YYYY", and we could identify that only because the day is greater than 12. For any date where the day is less than or equal to 12 you will never be able to verify whether it is intended to be "MM/DD/YYYY" or "DD/MM/YYYY".

Therefore, you don't know for sure that (1) is "MM/DD/YYYY", but you only assumed.