Date Conversion

[fallenangel6]

I have dates in 5 fields in the below format.

Apr 1 2019 12:00AM

I need to convert this into 1/04/2019 . how can i do this?

[Arthur_Fong]

Try this:

=DATE(date#(left('Apr 1 2019 12:00AM',index('Apr 1 2019 12:00AM',' ',3)),'MMM DD YYYY'),'D/MM/YYYY')

[Kas]

Try this

=Date(Date#(DateField, 'MMM DD YYYY hh:mm TT'), 'D/MM/YYYY')

or

=Date(Date#('Apr 1 2019 12:00 AM', 'MMM DD YYYY hh:mm TT'), 'D/MM/YYYY')

[fallenangel6]

Date Apr 1 2019 12:00AM has two blank space between Apr and 1

Apr<space><space>1 2019 12:00AM

[fallenangel6 · Accepted solution]

okay, i did it the long way.

makedate(right(left(HOLD_DATE,11),4),match(left(left(HOLD_DATE,11),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),SubField(left(HOLD_DATE,11),' ',3)) as HOLD_DATE

Not sure if this is efficient but if any of you have a better solution, then please do suggest.

[khan_imran]

Try this out:

Test:
LOAD * INLINE [
Date
Apr 11 2019 12:00AM
];

A1:

LOAD *,
mid(Date,9,4) as Year,
Left(Date,3) as Month,
mid(Date,6,2) as Dated,
makedate(mid(Date,9,4),match(Left(Date,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),mid(Date,6,1)) as Date1
Resident Test;

[Shayynee]

Hi

This works for me:

Date(Date#(Trim(Left(HOLD_DATE,11)),'MMM D YYYY'),'D/MM/YYYY') as HOLD_DATE