Conversion of date format

nadeembdhl99 · ‎2022-12-30

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 · ‎2022-12-30

Hi

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

nadeembdhl99 · ‎2022-12-30

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 · ‎2022-12-30

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 · ‎2022-12-30

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 · ‎2023-01-01

Please find the attached corresponding files for better understanding