Need Help Converting a Date in CSV file

[#1]

Good Morning All;

I am having a problem converting a Date from our vendor.  The file is coming to us as several CSV files.  Here are a few of the dates found in the IFADate field:

00:12.9

11:19.3

51:29.8

If I use Excel 2007 and format the cells using date (mm/dd/yy hh:ss) all translate as 1/0/1900 *****.  But if I use Excel 2010 the dates will be formatted properly.  Rather than "editing" the CSV files before load I would like QlikView to do it in the Load process.

Any Ideas?

Thanks

Mike

[maxgro]

=date(date#('51:29.8', 'YYYY:DD.MM'))

=date(date#(IFADate , 'YYYY:DD.MM'))

[hic]

Depends on what the numbers represent... are they minutes, seconds and decimal seconds, or? If so I would use

     Time#(IFADate, 'mm:ss.fff')

But QlikView still doesn't know of which month, day or hour these minutes belong. So you need to supply that, e.g. by

     Time( MakeTime(12) + Time#(IFADate, 'mm:ss.fff'), 'hh:mm:ss.fff' ) as Time

or

     TimeStamp( MakeDate(2014,07,15) + MakeTime(12) + Time#(IFADate, 'mm:ss.fff'), 'YYYY-MM-DD hh:mm:ss.fff' ) as TimeStamp

HIC

[#4]

Thanks...output looks different.  Where I was getting nothing now the dates are as follows:

00:12.9 translates as 36,869

09:12.4                as 40,151

27:12.4                as 46,725

So I am getting thing to change but not all dates are converting.  Only the 3 dates above.  There are 19 others

Thanks again.

[maxgro]

could you post the csv file with the dates (just the dates field)?

[#6]

Here you go...InvFollowup.csv

[PrashantSangle]

Hi,

Try this,

date(

  alt(

  date#(IFADate , 'YYYY:DD.MM'),

date#(IFADate , 'MM:DD:YYYY'),

date#(IFADate , 'DD:MM:YYYY'),

  )

, 'YYYY:MM:DD'  )

Regards,

[#8]

Here you go...InvFollowup.csv

Not sure what happened to the first

[PrashantSangle]

Hi,

I am not sure but your IFADate contain only time

there is no date values contain in IFADate

Regards,