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

Expression/ Difference between Date

Hi! How can I calculate the number of days between two dates? For example from 16.04.2013 to 20.08.2013?

Any suggestions greatly appreciated.

Andrea

22 Replies
maxgro
MVP
MVP

Hi Andrea

= date#('20.08.2013', 'DD.MM.YYYY') - date#('16.04.2013', 'DD.MM.YYYY')

Sokkorn
Master
Master

Hi Massimo,

If your data is a Date field, then simply use =[Date2]-[Date1]

If your data is a Text field (ex. 20.08.2013), then you need to format it first before calculation

=Date#([Date2],'dd.MM.yyyy')-Date#([Date1],'dd.MM.yyyy')

You may use Interval as well.

=Interval([Date2]-[Date1],'D hh:mm')

=Interval(Date#([Date2],'dd.MM.yyyy')-Date#([Date1],'dd.MM.yyyy'),'D hh:mm')

Regards,

Sokkorn

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

=Interval(Date#([ToDate], 'DD.MM.YYYY')-Date#([FromDate],'DD.MM.YYYY'),'D')


Regards,

Jagan.

Not applicable
Author

Thank you for your help

Not applicable
Author

Hi! If I have 16.04.2013 as value in "start date" field and 28.02.2014 as value in "end date", and I only consider the interval from 01.01.2013 to 31.12.2013, how can I write my expression?

Thank you for any help you can provide in this situation.

Andrea

Sokkorn
Master
Master

Hi Andrea,

Use this in text object:

=YearStart(Today())     -> Return 01-01-2014

=YearEnd(Today())     -> Return 31-12-2014

=YearStart(AddYears(Today(),-1))     -> Return 01-01-2013

=YearEnd(AddYears(Today(),-1))     -> Return 31-12-2013

Hence, we can use interval

=Interval(YearEnd(AddYears(Today(),-1))-YearStart(AddYears(Today(),-1)),'D')     -> Return 364

Regards,

Sokkorn

jagan
Luminary Alumni
Luminary Alumni

HI,

Try like this

=Interval(YearStart(Date#([ToDate], 'DD.MM.YYYY'))-YearEnd(Date#([ToDate],'DD.MM.YYYY')),'D')

Hope this helps you.

Regards,

Jagan.

nizamsha
Specialist II
Specialist II

TableA:

LOAD * INLINE [

    Date,Date2

    01.01.2012,01.01.2013

     01.02.2012,01.03.2012

];

LOAD    Date  as Date1,

Date2 as Date3,

Date2-Date  as Date5,

Date#( Date2 ,'DD.MM.YYYY')-Date#( Date ,'DD.MM.YYYY')as Date6

Resident TableA;

nizamsha
Specialist II
Specialist II

TableA:

LOAD * INLINE [

    Date,Date2

    01.01.2012,01.01.2013

     01.02.2012,01.03.2012

];

LOAD    Date  as Date1,

Date2 as Date3, 

Interval#( Date#( Date2 ,'DD.MM.YYYY')-Date#( Date ,'DD.MM.YYYY'),'D') as Date6

Resident TableA;