Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to strip off timestamp from a date field

Hi all,

I have a field in an Excel sheet that has date values stored in this way :

Accepted Date

September 20, 2016 10:31:55 AM GMT+05:30
October 14, 2016 8:16:52 PM GMT+05:30
November 21, 2016 7:05:30 PM GMT+05:30
November 23, 2016 6:40:42 PM GMT+05:30

However, I need to display the date in the 'mm/dd/yy' format in my chart, without the timestamp. What should I do?

Thanks in advance,

Kanchana

17 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Here is the solution.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
shubham_singh
Partner - Creator II
Partner - Creator II

try this

date(date#(left([AcceptedOn],index([AcceptedOn],',')+5),'MMMM DD, YYYY'),'MM/DD/YY')

Not applicable
Author

Hi Kaushik,

Thanks, but I am using QlikSense Desktop 3.1. So cannot use the qvw file. Do you have an alternative?

Regards

Kanchana

Chanty4u
MVP
MVP

try same script in qliksense

LOAD

    AcceptedOn,

    id

FROM [lib://ss/Date Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Note: Which is highlated paste ur path.

Not applicable
Author

Try this

Date(Floor(Date(Date#(Left(AcceptedOn,Len(AcceptedOn)-10),'MMMM DD, YYYY hh:mm:ss TT'))),'DD/MM/YYYY')

Anonymous
Not applicable
Author

Try like this:

Date(Floor(Date(Date#(Left(AcceptedOn,Len(AcceptedOn)-10),'MMMM DD, YYYY hh:mm:ss TT'))),'DD/MM/YYYY')


i think its going to work and also associate with the older dates with out any data lost.



Thanq.

Not applicable
Author

Thank you, this works.

rajendra1918
Creator
Creator

Thanks Kalyani Reddy.

Function shared by you worked for us.