Convert to Date format/Multiple timestamps

[Anonymous]

HI all,

anyone can help to convert below format to standard date format? I used several approaches but I was not successfull. Thanks

The input format is here:

Thu 02-Feb 08:03 PST  |  Thu 02-Feb 16:03 CET  |  Thu 02-Feb 23:03 SGT

Thank you for help

Martina

[MK_QSL]

What output you are expecting?

Year is not mentioned here... Do you want to link this to current year?

[Anonymous]

Output format desired ideally 'MM/DD/YYYY' where Year is always current year. thanks

[surendraj]

In text box...

Try with

=(Date(date#(SubField('Thu 02-Feb 08:03 PST',' ',2),'DD-MMM'),'DD-MM'))&'-'&Year(today())

[vinieme12]

Try as below

LOAD *,DATE(Date#(subfield(left(TIMESTAMPIS,len(TIMESTAMPIS)-10),' ',2),'DD-MMM'),'MM/DD/'&year(today())) as DateIS INLINE [

TIMESTAMPIS

Thu 02-Feb 08:03 PST

Thu 02-Feb 16:03 CET

Thu 02-Mar 23:03 SGT

];

[sdmech81]

HI,

Use String modification function(like Left,right,mid,subfield) inside Date function(Date,Timestamp etc) to sort this out.

Because your date is not in proper format so QLik qill treat it as string so you have to make it as date as said above..Hope it helps

Sachin

[Anonymous]

Will this work when each load would have different content? Its not always exactly 2nd Feb. This was just example

Thanks

[vinieme12]

Yep it'll work

Change the dates and try

[Anonymous]

Thank you but apparently Im doing something wrong, not sure. I tried renaming "timestampis" to my column header, did not work.

Even still Im loading data for 2nd Feb & 6th Feb but scipt rolls result only for 2nd feb.

Thanks again

[surendraj]

Please load the .xls file for testing..to check wheather the dates are formated correct or not.