Reading Dates

[#1]

Experts ,

Many times I faced dates issues but none of old techniques worked. Could you please help me to read the dates here.

[Output]:

LOAD 

ALT(DATE#(Date,'D/M/YYYY'),DATE#(Date,'DD/M/YYYY'),DATE#(Date,'D/MM/YYYY'),DATE#(Date,'DD/MM/YYYY'))as Date,

Sales

RESIDENT [Dress Sales] ;

But My Output is:

My Source is:

[jagan]

Hi,

Try like this

LOAD

*,

Alt(Date(DATE#([created Date],'D/M/YYYY')), Date(Num([created Date]))) AS Formatted_Date

FROM DataSource;

Regards,

Jagan.

[#3]

replace all lines in your application with below code and run it

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

LOAD Date

FROM

[Sample Dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#4]

Still Some dates are missing

[#5]

please send me ur teamviewer id to my gmail account.  vijayan.vipin@gmail.com

[#6]

In the main tab of your script, change the variable to this one:

SET DateFormat='DD/MM/YYYY';

and you can load your excel normally, you dont need to use the date command in the field Date.

It should work...there is 23 dates just like your Excel File.

[#7 · Accepted solution]

Hi Renato,

You might be true sorry for not testing your option but what gave me satisfied solution is,

LOAD

(if ((isnum(Date) = -1),Date,Date(Num#(Date)))) as Date,

Sales

;

num#() helped me here

[#8]

I am sorry , I dont get your problem then. You will find in the attachment how I reached to the result.