Convertion of diff date format in to unique format

[#1]

Hi All,

I have date field in the attached excel. it is in DD-MM-YYYY and in string format. i need to convert everything in to DD-MM-YYYY format.

For Instance in the attached excel i have 01-08-2013, 01-09-2013 and some string dates like 15/01/2013 , 16/01/2013 but all are belongs to january month.

Expected result is 08-01-2013, 09-01-2013, 15-01-2013, 16-01-2013

Kindly help me to resolve this.

Thanks in Advance.

Mahi.

[Gysbert_Wassenaar]

See here for a solution

[#3]

Thanks Gysbert,

But again am facing an issue, am getting dates for strings not for num. for instance in attached excel i have

01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013.

But am not getting this values. kindly help me to get this data also in DD-MM-YYYY format.

Thanks in Advance.

Mahi.

[#4]

Try this

Hope you are looking this

Date(makedate(right(fieldname,4),mid(fieldname,4,1),subfield(fieldname,'/',1)),"DD-MM-YYYY")

Regards,

Amay

[#5]

Actually In excel, the remaining dates are in dd-mm-yyyy format but i want that in mm-dd-yyyy format.

I Mean all the dates  ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 )belongs to January Month.

Kindly help me to resolve this.

Thanks in Advance.

Mahi

[#6]

Hi

As per my understanding you required to convert string to Date format then try this

Date(Date#(DateField,'DD/MM/YYYY'),'DD-MM-YYYY')

sorry if i'm wrong in understanding

Best of luck

[manideep78]

Hi see below code

LOAD Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY') as A,

     B

FROM

C:\Users\Manideep\Downloads\comm_excel.xlsx

(ooxml, no labels, table is Sheet1);

A	B
18-01-2013	AAA
22-01-2013	AAA
28-01-2013	AAA
16-01-2013	AAA
31-01-2013	AAA
15-01-2013	AAA
30-01-2013	AAA
	AAA
	BBB

[#8]

Hi Manideep and satish, thanks for your reply. This is ok, am getting string format dates as date format. but few dates i have in DD-MM-YYYY  ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 ) format i need to convert that in to MM-DD-YYYY format. i need to convert all the above mentioned dates belongs to JAN month.

Thanks in Advance,

Mahi.

[manideep78]

try this

LOAD if(Month(Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY'))=1,Date(Date#(A,'DD/MM/YYYY'),'MM-DD-YYYY'),Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY')) as A,

     B

FROM

C:\Users\Manideep\Downloads\comm_excel.xlsx

(ooxml, no labels, table is Sheet1);

[#10]

Hi Manideep,

Thanks for your effort, again am not getting the data  ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 ) in that date field.

I need to include that data also which are in date month year format ( i need to convert that into month date year)

Thanks in Advance,

Mahi