convert to date

[Anonymous]

Just attached the Date file in Excel. I tried the below suggestions, but no luck.

Hi, I have below data in Excel, the default format is not date. I would like to  keep only the date and change the format to  MM/DD/YYYY after loading into Qlik. I tried date(floor(date#(Date,'M/D/YYYY h:mm')),'MM/DD/YYYY'), but no luck.

Any suggestions? Thanks!

PS, in Excel it actually is 9/30/2015  12:00:00 AM, but displayed as 9/30/2015 0:00

Date

6/30/2015 0:00

7/31/2015 0:00

8/31/2015 0:00

9/30/2015 0:00

10/31/2015 0:00

11/30/2015 0:00

12/31/2015 0:00

[Anonymous]

try

date(floor(date#(YOURDATEFIELD,'M/D/YYYY h:mm')),'MM/DD/YYYY')

[Anonymous]

I tried using Date# and Floor, no luck

[Anonymous]

for me it works, I forced text()  because I don't know whats your default date format...

If your default dateformat fits, then you don't have to convert anything at all...

LOAD date(floor(date#(Datefield,'M/D/YYYY h:mm')),'MM/DD/YYYY') as Datefield;
LOAD text(Datefield) as Datefield INLINE [
Datefield
6/30/2015 0:00
7/31/2015 0:00
8/31/2015 0:00
9/30/2015 0:00
10/31/2015 0:00
11/30/2015 0:00
12/31/2015 0:00
];

[Anil_Babu_Samineni]

Can you provide Excel file values. And look below may be help

QlikView Date fields

Get the Dates Right

Why don’t my dates work?

[Anonymous]

Hi Ze Li,

I have created a datasheet with you dates on excel and uploaded them to Qlik Sense and true enough, the fields are saved as your order.

Do follow the steps below :

1. Click 'edit this table' in the Data Manager

2. Click the date symbol

3. Click the display format to MM/DD/YYYY

Cheers, let me know if this works.

Regards,

Gaston

[Anonymous]

I realised that you are using Qlik View instead, my apologies.

Still, if there is any functionality of Qlik View which allows you to prepare the data before loading it in, it should be capable of changing the display format. Otherwise, date(date#(Date,'M/D/YYYY h:mm'),'MM/DD/YYYY') should work.

You can try to troubleshoot this first by keying in date#(Date,'M/D/YYYY h:mm') and seeing it QlikView recognises if as the correct date format (output should be a number), play around with values such as M/DD/YYYY h:mm or M/D/YYYY hh:mm, once you get the first part right, you should be able to display in as your desired format thereafter.

Regards,

Gaston

[Anonymous]

Try This one

date(timestamp#([start_date],'M/D/YYYY h:mm'), 'MM/DD/YYYY')

[brijeshvma]

Provide Some Data in Excel

[brijeshvma]

Hi,

If data in AM and PM as well..So try with This

LOAD date(floor(date#(Datefield,'M/D/YYYY h:mm TT')),'MM/DD/YYYY') as Datefield;

As well its not working so please provide Excel for the same