topic Re: Convert Date and calculate duration in QlikView

Convert Date and calculate duration

marieclaire — Wed, 25 Nov 2020 16:16:04 GMT

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 !

eduardo_dimperio — Wed, 16 May 2018 17:38:44 GMT

1) Floor(YourDate)

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

shree909 — Wed, 16 May 2018 17:38:59 GMT

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 — Wed, 16 May 2018 19:57:38 GMT

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 — Sun, 20 May 2018 15:26:29 GMT

Thank you Eduardo for this very clear solution.

It works perfectly !

eduardo_dimperio — Mon, 21 May 2018 13:00:32 GMT

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

qlikviewwizard — Mon, 21 May 2018 13:57:01 GMT

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

=DATE(date#(<YourDateFiled>,'DD/MM/YYYY HH:SS'),'DD/MM/YYYY')

Interval( A-B, 'D' )

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