Date Conversion Not Working

[kateevans2]

The original field being loaded is a text field in excel and shows as ,for example "01- JUL-13" i need this to be displayed as 01/07/13 and mappable to another date of the same format, however all the date conversion i have tried do not appear to amend the date format or will bring through a null value instead.

I have attempted to use the following 

M_NUMBER,

ND_DATE,
Date#(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE2,
num(ND_DATE) as ND_DATE3,
date(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE4,
Date(num#(ND_DATE), 'DD/MM/YYYY') as ND_DATE5
Date(Floor(ND_DATE), 'DD/MM/YYYY'')as ND_DATE6,
Date(ND_DATE, 'MM/DD/YYYY') as ND_DATE7,

Date(Date#(ND_DATE, 'DD-MMM-YYY), 'DD/MM/YYYY')as ND_DATE8

original Data format in attached file any assistance gladly received 

[sunny_talwar]

Try this

Table:
LOAD Date(Date#(ND_DATE, 'DD-MMM-YY')) as ND_DATE2,
	 ND_DATE,
     CLAIM_NUMBER
FROM
[..\..\Downloads\Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);

[ishanbhatt]

Hello,

Use below formula.

Date(Floor(num(Date field)))  as Date.

Set Date format in the start.

[kateevans2]

Thank you the answer was a cross of your code and an earlier attempt i had

Date(Date#(ND_DATE,'DD-MMM-YY'), 'DD/MM/YYYY') 

many thanks for your help 

[sunny_talwar]

If you set your environmental variable with the required date

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;

All you would need to do is this

Date(Date#(ND_DATE,'DD-MMM-YY')) as ND_DATE