Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

convert to date

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
1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Ze Li,

Maybe try,

Date(SubField(Data,' ',1),'MM/DD/YYYY')

View solution in original post

10 Replies
Anonymous
Not applicable
Author

try

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

Anonymous
Not applicable
Author

I tried using Date# and Floor, no luck

Anonymous
Not applicable
Author

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

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

1.png

2. Click the date symbol

2.png

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

3.png

Cheers, let me know if this works.

Regards,

Gaston

Anonymous
Not applicable
Author

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
Not applicable
Author

Try This one


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

brijeshvma
Partner - Creator
Partner - Creator

Provide Some Data in Excel

brijeshvma
Partner - Creator
Partner - Creator

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