difference b/w the 2 dates in years

[PradeepReddy]

Hi All,

      I want to caluclate the difference b/w the 2 dates and the result should be in years. So that I can create the bucket as shown below. I am able to calulate this in no.of days, after that based on the days range i am doing the calculation. But the tricky part is there might be chance of Leap years. So how can I handle this scenario.

Bucket:

<1 yr

1-5 yrs

6-10 yrs

>10 yrs 

Expression I used:

IF(TODAY()-REGISTRATION_DATE < 365,'< 1',
     IF(TODAY()-REGISTRATION_DATE >= 365 AND TODAY()-REGISTRATION_DATE <= 1825,'1 - 5',

          IF(TODAY()-REGISTRATION_DATE > 1825 AND TODAY()-REGISTRATION_DAT) <= 3650,'6 - 10',

               IF(TODAY()-REGISTRATION_DATE > 3650,'> 10'))))  AS TEMP_DATE_RANGE

Thanks in advance.

Pradeep

[SunilChauhan]

use below code

IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY')< 1,'< 1',
     IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') >= 1 AND Interval(TODAY()-REGISTRATION_DATE ,'YYYY')) <= 5,'1 - 5',

          IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') > 6 AND Interval(TODAY()-REGISTRATION_DATE ,'YYYY')) <= 10 ,'6 - 10',

               IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') > 10,'> 10'))))  AS TEMP_DATE_RANGE

hope this helps

[SunilChauhan]

or you can use

Year(TODAY())-Year(REGISTRATION_DATE)

or

age(TODAY, REGISTRATION_DATE)  make sure format of both today and REGISTRATION_DATEshould be same

inplace of

TODAY()-REGISTRATION_DATE

in expression

hope this helps

[maxgro]

perhaps you can use age function

age(timestamp, date_of_birth)

Returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

Examples:

age('2007-01-25', '2005-10-29') returns 1 

age('2007-10-29', '2005-10-29') returns 2 

[PradeepReddy]

TODAY()) - Year(TODAY()-365)   and   (TODAY()) - Year(TODAY()-366)  returning the value as '1'.

For the first expression it should not be '1', as the difference is not  more than one year.

[Roop]

You can either use Massimo Grossi's approach but the reality of the situation is that it is going to make very little difference in overall scope. Obviously, there are at least 1 leap year in the 5 year category and at least 1 in the 6 to 10 year category so those numbers can be adjusted slightly.

What I would difinitely do is to set a RangeNumber instead of descriptors. This is then linked to the inline table set out below. This means that you are able to change descriptions easily and perhaps more importantly sort the list in a listbar.

In line load for RangeNumber

AgeRange:

Load * Inline

[RangeNumber, Bucket

1, < 1 year

2, 1 to 5 year

3, 6 to 10 year

4, >10 yrs

];

Hope this helps

[SunilChauhan]

so what result should you expect for this cases

i mean fr below

TODAY()) - Year(TODAY()-365)   and   (TODAY()) - Year(TODAY()-366)  returning the value as '1'.

For the first expression it should not be '1', as the difference is not  more than one year.

[PradeepReddy]

I am expecting the following results....

Year(TODAY()) - Year(TODAY()-364)   = 0

Year(TODAY()) - Year(TODAY()-366)   = 1;

but both expressions giving the value as '1'.

[SunilChauhan]

• in that case use below

age(today(),(today()-364))

age(today(),(today()-366))

hope this hellps

[Roop]

They will both return 2014 and 2013 respectively and so will return 1