Conversion of date format

[nadeembdhl99]

Dear team,

I have an issue.

The date column from my table  has both formats in it i.e., 'dd-mm-yyyy' and 'mm-dd-yyyy',

how to resolve it?

ex:

DD-MM-YYYY

01-12-2022

and 

MM-DD-YYYY

02-12-2022

[BrunPierre]

Hi

How are you able to tell the difference between the two formats?

[nadeembdhl99]

The data we received is only for Dec month..

But in list box its showing all the months.

i.e., 12-01-2022 to 12-12-2022

And remaining dates as 13-12-2022 till 28-12-2022

Format: DDMMYYYY for all dates

[BrunPierre]

Since you have different date formats in one field, you can define your desired output using the Alt() function.

Perhaps as below

Date(Alt(Date#(DateColumnWithTwoDifferentFormats,'DD-MM-YYYY'),Date#(DateColumnWithTwoDifferentFormats,'MM-DD-YYYY')),'DDMMYYYY') as NewDateField

[nadeembdhl99]

I've tried this code but didn't workout...

As the other format takes month as date and date as month..

Like if its 12-10-2022

It'll show 12th of october

but it actually is 10th of december.

[nadeembdhl99]

Please find the attached corresponding files for better understanding