Qlikview - Difference between two dates in years, months, days

[#1]

Hi,

I need to calculate difference between two dates in years, months, days.

I've been searching and have not found a solution to get the difference between two dates in years, months, and days .There are lots of solutions, but they only offer the difference in the format of either days OR months OR years, or they are not correct (meaning not taking care of actual number of days in a month or leap years, etc). Is it really that difficult to do that?

I've had a look at:

https://community.qlikview.com/thread/69414

but does not returns correct number.

Any idea?

[Peter_Cammaert]

I guess this can be pretty difficult to calculate in a generic way. You may have to supply a few business rules that must be applied in your situation.

For example,

• If Year-1 is a leap year, then how should we express the difference between Feb 29, Year-1 and Feb 28, Year?
• If Year is a leap year, how should we express the difference between Feb 29 and Mar 31? And Feb 28 and Mar 31?
• And so on...

The catch will be in the fact that you should be able to reverse the calculation using the same result, and get the original data.

Peter

[sunny_talwar]

See if this link help:

How Do I Get the Number of Months Between Two Dates?‌

It gives the month difference, adding Day should not be very difficult. May be if you can provide a sample, we might be able to give you that part as well.

[effinty2112]

Hi,

          If you have a start date and an end date as variables vStart and vEnd then try this variable:

vWholeMonths =

12*Year('$(vEnd)')+Month('$(vEnd)')

-(12*Year('$(vStart)')+Month('$(vStart)'))

- if(Day('$(vStart)')>Day('$(vEnd)'),1,0)

From vWholeMonths these variables appear to give what you need:

vYears = Div($(vWholeMonths),12)

vMonths = Mod($(vWholeMonths),12)

vDays =

if(Day('$(vStart)')>Day('$(vEnd)'),

Round(MonthEnd('$(vStart)'))- Date('$(vStart)')+Day('$(vEnd)')-1,

Day('$(vEnd)')-Day('$(vStart)'))

I was annoyed that I had to resort to using Round in vDays.

Hope this helps

Andrew

[clovati]

It's all right!

Just one little note: If I have two dates: from may 1 2015 to may 3 2015 it gives me 2 days but they are 3 (in my case) because the first and the last days are included in my range of days. In this case I've added 1 to the number of days.

Thanks a lot! I saved loto of time to resolve it!

[eddysanchez]

Arabela,

=If(day([End Date])>=day([Start Date]),

div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Years'

&' '&

mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))),12) & ' Months'

&' '&

([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date]))))) & ' Days'

,

div(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Years'

&' '&

mod(((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1,12) & ' Months'

&' '&

([End Date]-AddMonths([Start Date],((year([End Date])*12)+month([End Date])) - (((year([Start Date])*12)+month([Start Date])))-1)) & ' Days'

)

Eddy