topic Re: How to Correct Different Format Dates in a Column via DAX? in DAX Commands and Tips

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

RahimZulfiqar87 — Fri, 30 Jun 2023 14:03:20 GMT

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

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

tamerj1 — Fri, 30 Jun 2023 14:47:09 GMT

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.

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

RahimZulfiqar87 — Fri, 30 Jun 2023 14:50:35 GMT

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

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

tamerj1 — Fri, 30 Jun 2023 15:06:55 GMT

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.

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

tamerj1 — Fri, 30 Jun 2023 15:05:45 GMT

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.