Convert Date and calculate duration

[marieclaire]

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 · Accepted solution]

1) Floor(YourDate)

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

[shree909]

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]

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]

Thank you Eduardo for this very clear solution.

It works perfectly !

[eduardo_dimperio]

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]

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

Or

=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