Difference between two dates in Year,Month and Days

[Anonymous]

I have tried calculating difference between two dates example Qvw is attached it works for every date but only if the the end date's month is greater than the start. please see the image which is the problem.

[auzzie4185]

I agree that the correct answer is incorrect. I noticed it because it gave me 0 years 0 months 0 days when it was exactly 1 year. I was looking at ways to correct it when I scrolled down and found yours!!! 

Thank you!

Auzzie 

[SiO2y]

If you have the opportunity to calculate in the load script, a preceding load makes the script more manageable - and easier to correctly add 's' for multiple years/months/days etc.

This script will only show a value for years, months or days if there is one (e.g. if the duration is exactly 2 years and 1 day, it will output "2 years, 1 day" rather than "2 years, 0 months, 1 days")

Examples:

• 0 years, 0 months, 11 days: "11 days"
• 0 years, 2 months, 1 day: "2 months, 1 day"
• 1 year, 3 months, 0 days: "1 year, 3 months"
• 0 years, 1 month, 0 days: "1 month"
• 1 year, 2 months, 3 days: "1 year, 2 months, 3 days"

LOAD If([_y] = 0, '', [_y] & If([_y] = 1, ' year' , ' years' ) & If([_m] > 0 or [_d] > 0, ', ', ''))
& If([_m] = 0, '', [_m] & If([_m] = 1, ' month', ' months') & If([_d] > 0, ', ', ''))
& If([_d] = 0, '', [_d] & If([_d] = 1, ' day' , ' days' )) as [Duration];

LOAD If(Day([Support to]) >= Day([Support from]),
Div(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) , 12),
Div(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1, 12)) as [_y]
, If(Day([Support to]) >= Day([Support from]),
Mod(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) , 12),
Mod(((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1, 12)) as [_m]
, If(Day([Support to]) >= Day([Support from]),
([Support to] - AddMonths([Support from], ((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))))),
([Support to] - AddMonths([Support from], ((Year([Support to]) * 12) + Month([Support to])) - (((Year([Support from]) * 12) + Month([Support from]))) - 1))) as [_d]