Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Andrea
= date#('20.08.2013', 'DD.MM.YYYY') - date#('16.04.2013', 'DD.MM.YYYY')
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
Hi,
Try like this
=Interval(Date#([ToDate], 'DD.MM.YYYY')-Date#([FromDate],'DD.MM.YYYY'),'D')
Regards,
Jagan.
Thank you for your help
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
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
HI,
Try like this
=Interval(YearStart(Date#([ToDate], 'DD.MM.YYYY'))-YearEnd(Date#([ToDate],'DD.MM.YYYY')),'D')
Hope this helps you.
Regards,
Jagan.
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;
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;