Help with date formats

[#1]

Hi,

I wondered if someone can help me with this issue I am having with Date/Time fields I am importing into Qlikview from Excel.

I can't seem to get them all to convert to date time, I'm sure I must be doing something silly but I have been puzzling over this for hours now and can't figure it out. I have uploaded anonymised excel and qvw.

I hope someone can help!

Thanks,

Kate

[sacosta5]

Hi Katy,

maybe...

=Timestamp#(Text([Ref Date/Time]),'DD/MM/YYYY HH:MM').

[sunny_talwar]

I have not looked at the sample, but I would not use MM (Capital Ms) for Minutes because MM are for Months and mm are for minutes

May be this:

=Timestamp#(Text([Ref Date/Time]),'DD/MM/YYYY hh:mm').

[Clever_Anjos]

Try this:

Ashford:

//CONCATENATE (Main)

LOAD 

timestamp(trim([Ref Date/Time]),'DD/MM/YYYY hh:mm') as [Ref Date/Time]

FROM [Testing.xlsx] (ooxml, embedded labels, Table is [$(m)]);

NEXT

[sunny_talwar · Accepted solution]

Try this:

=Alt(TimeStamp([Ref Date/Time]), TimeStamp(Timestamp#([Ref Date/Time], 'DD/MM/YYYY hh:mm')))

[#6]

Thanks so much Sunny, this works perfectly!!

[#7]

HI Katy,

You can use the following expression

=Timestamp(Timestamp#([Ref Date/Time],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss')

I attach also qvw changed with the given expression

Regards

Gennaro

[sunny_talwar]

Awesome