Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

marieclaire
New Contributor III

Convert Date and calculate duration

Hi the community !

I have to resolve 2 issues and i do not manage for now to do it :

Format Date :

I do not manage to format this Date/Timestamp : 02/10/2014 08:55      to    02/10/2014

I tried many functions as

Date, Date(num(...)), Timestamp or

MakeDate (02/10/2014 08:55,'DD/MM/AAAA')

But it doesn't work.

Duration between 2 dates :

I have to calculate a duration, and convert it in number of year, or months.

What is/are the function(s) that allow(s) that ?

Thank you for your help !

Tags (1)
1 Solution

Accepted Solutions
eduardo_dimperi
Valued Contributor II

Re: Convert Date and calculate duration

1) Floor(YourDate)

2) Interval (A-B,'D') if you want count days between dates

6 Replies
eduardo_dimperi
Valued Contributor II

Re: Convert Date and calculate duration

1) Floor(YourDate)

2) Interval (A-B,'D') if you want count days between dates

shree909
Valued Contributor II

Re: Convert Date and calculate duration

HI,

can you try this

  • Date(Ceil(Timestamp#(02/10/2014 08:55,'MM/DD/YYYY hh:mm)),'MM/DD/YYYY')  AS New Date

The date can be replaced with the Field name..

marieclaire
New Contributor III

Re: Convert Date and calculate duration

Thank you Sree. It works perfectly :

Date(Ceil(Timestamp#([field name],'DD/MM/YYYY hh:mm'))-1,'DD/MM/YYYY')

I just added '-1' to get the same day instead of the day after.

Thanks again.

marieclaire
New Contributor III

Re: Convert Date and calculate duration

Thank you Eduardo for this very clear solution.

It works perfectly !

eduardo_dimperi
Valued Contributor II

Re: Convert Date and calculate duration

Hi Christiane, im very happy that it worked for you. Mark some answer as Right and if you need more help don't hesitate to ask.

Good Luck

Arjunarao
Honored Contributor II

Re: Convert Date and calculate duration

=DATE(date#('02/10/2014 08:55','DD/MM/YYYY HHSmiley FrustratedS'),'DD/MM/YYYY')

Or

=DATE(date#(<YourDateFiled>,'DD/MM/YYYY HHSmiley FrustratedS'),'DD/MM/YYYY')

Interval( A-B, 'D' )

where A=97-08-06 09:00:00 and B=96-08-06 00:00:00

Community Browser