How to calculate date diff in Year month & days format

[ajaysingh29]

Hi All,

I have data with DOJ, I wanted to calculate No of Year, Month & days for the employees in straight table .

Expected format is : 1 Year 1 Month 16 days.

Attached test qvw file. How to achieve this?

Thanks!

[sunny_talwar · Accepted solution]

Try this

=IF

(Today() <= AddYears(DOJ,1),'0 Years ',

Age(Today(),DOJ) & ' Years ')

&

IF(MONTH(Today()) = MONTH(DOJ),'0 Months ',

IF(DAY(DOJ) < DAY(Today()), fabs(MONTH(Today())-Month(DOJ)) & ' Months ',

((MONTH(Today())+12)-Month(DOJ))-1  & ' Months '

))

&

IF(MONTH(Today()) = MONTH(DOJ),DAY(Today()) - DAY(DOJ) & ' Days',

IF(DAY(Today()) > DAY(DOJ),DAY(Today()) - DAY(DOJ) & ' Days',

DAY(DATE(Interval(Today() - DOJ)))-1 & ' Days'

))

Took it from here:

Difference between two dates in Year,Month and ... | Qlik Community

[ajaysingh29]

Thanks Sunny!

[migueldelval]

Hi Ajay,

Check this please

=left((Today()-DOJ)/365,1)&' year '&(left(((Today()-DOJ)-(left(((Today()-DOJ)/365),1)*365))/30,1))&' Months'&((((Today()-DOJ)-(left(((Today()-DOJ)/365),1)*365)))-(left(((Today()-DOJ)-(left(((Today()-DOJ)/365),1)*365))/30,1)*30))&' days '

Regards

Miguel del Valle

[ajaysingh29]

Hi Migule,

Thank you for your reply. Your solution is almost correct & working. However, in some case its not showing exact date diff.  Like if I select DOJ 5/1/16 its showing 1 Year & 1 Month. however with Sunny's solution its showing 1 Year, 0 month & 30 Days.

Again, thank you for your efforts and help!