topic Re: How to calculate date diff in Year month & days format in QlikView

How to calculate date diff in Year month & days format

ajaysingh29 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: How to calculate date diff in Year month & days format

sunny_talwar — Wed, 31 May 2017 11:14:52 GMT

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

Re: How to calculate date diff in Year month & days format

ajaysingh29 — Wed, 31 May 2017 11:28:42 GMT

Thanks Sunny!

Re: How to calculate date diff in Year month & days format

migueldelval — Wed, 31 May 2017 11:39:05 GMT

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

Re: How to calculate date diff in Year month & days format

ajaysingh29 — Wed, 31 May 2017 11:50:02 GMT

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!