Expression/ Difference between Date

[#1]

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

[maxgro]

Hi Andrea

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

[Sokkorn]

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]

Hi,

Try like this

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

Regards,

Jagan.

[#5]

Thank you for your help

[#6]

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]

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]

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]

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]

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;