Mixed date format in a single field

[Kohli]

I have date field in my xl file. Like 

Date:

DD.MM.YY
DD.MM.YYYY
DD-MM-YY
DD-MM--YYYY

I want  convert into single format like DD-MM-YY. please suggest me.

[Channa]

=date(date#(datefield,'DD/MM/YY'),'DD/MM/YY')

[PrashantSangle]

use alt().
see in help menu for details.

Regards,
Prashant Sangle

[pradosh_thakur]

assuming your date field's name is date_field and theya re real dates but not string try the below

DATE(date_field,'DD-MM-YY')

[pradosh_thakur]

if they are strings try the below

alt(date(date#(purgechar(date_field,'.-')'DDMMYYYY'),'DD-MM-YY'),
date(date#(purgechar(date_field,'.-')'DDMMYY'),'DD-MM-YY')
)

[mdmukramali]

Hi,

As suggested by @PrashantSangle you can try to use Alt function.

Syntax:

Date(Alt(Date#(Date, 'FirstFormat'), Date#(Date, 'SecondFormat'), 'RequiredFormat') as Date

Example:

Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

Date#(MixDate,'M/D/YYYY'),

Date#(MixDate,'D/M/YYYY'),

Date#(MixDate,'YYYYMMDD'),

Date#(MixDate,'DD.MM.YYYY'),

Date#(MixDate,'YYYY-MM-DD')

) as MixDate

Thanks,

Mohammed Mukram

[Frank_Hartmann]

try this:

Alt( 
 Date(Date#(Date,'DD-MM--YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD-MM-YY'),'DD-MM-YY') 
 ) as Date