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

1 Solution

Accepted Solutions
Not applicable
Author

Try this

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

View solution in original post

17 Replies
kkkumar82
Specialist III
Specialist III

Use Date(Your field, 'MM/DD/YY') in your load script or in front end

if you remove time stamp part from your date field completely then use

Date(Floor(your field),'MM/DD/YY')

Not applicable
Author

Hi Kiran,

Thanks for your reply. I have tried using this expression in both the ways - in my load script or in my chart, but it does not work for some strange reason. I cannot figure why. Could it be that the field has to be formatted in a particular way in the Excel sheet itself?

Thanks,

Kanchana

kkkumar82
Specialist III
Specialist III

Can you post a sample excel with the date fields or your sample qvw

miskin_m
Partner - Creator
Partner - Creator

Hi

If it is viable for you to remove GMT part of the field by subfield(FieldName,'GMT',1)  in script, then use expression as

=date(Date#(FieldName,'MMMM DD, YYYY hh:mm:ss TT'),'DD/MM/YY')

Anonymous
Not applicable
Author

Hi,

i think this is helpful to you...

try this...

=(Date#(SubField(accepteddate,',',1) & Left(SubField(accepteddate,',',2),5),'MM-DD-YYYY'))

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

If it is coming as Date while loading the data in Qlik, you simply need to use below function.

Date(Floor(Fieldname),'MM/DD/YY') as NewDate

If it is coming as Text while loading the data in Qlik, then use below script.

Date(Floor(Date(Date#(Fieldname,'MMMM DD, YYYY hh:mm:ss TT'))),'MM/DD/YY') as NewDate


Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Can you send me the sample data, so that it would help to solve

Not applicable
Author

Hi Kalyani,

This works partially! There are 2 problems :

1. The formatting that you have doesn't work, this just strips off the timestamp and leaves the date behind, as-it-is.

2. The data associated with the older dates are lost.

i.e , if my table is as originally follows :

DateDefect ID
September 20, 2016 10:31:55 AM GMT+05:30MR00190080
October 14, 2016 8:16:52 PM GMT+05:30MR00190945
November 21, 2016 7:05:30 PM GMT+05:30MR00190946
November 23, 2016 6:40:42 PM GMT+05:30MR00190947
November 23, 2016 6:40:42 PM GMT+05:30MR00190948
October 14, 2016 8:16:52 PM GMT+05:30MR00190949

There are 2 defects logged on October 14. But now, with the timestamp being stripped off, when I plot, for example, Count([Defect ID]) against this new date as dimension, the values are all wrong.

Regards,

Kanchana

Not applicable
Author

Hi,

I have attached the sample Excel for you to look at. What I want is to strip off the time stamp from the date field and plot the number of Defect IDs on a particular date as a bar graph.

Regards,

Kanchana