Date differences in years & months (1 Viewer)

[linanderson]

I have set up an expression to calculate the current contract length between start & cancellation date:

ContAge: Format([ContCancDate]-[ContStartDate],"yy"" yrs ""mm"" mths""")

It is important to have years and months shown.

Works fine apart from those contracts which are cancelled from their StartDate. This can happen. The result is 99 yrs 12 mths. What I would like to see is 0 mths.

I sure there is a simple way around this, but it has passed me by at the moment.

Would appreciate any ideas.

Thanks

 

[raskew]

No claims made for elegance, but I've tested the following and it works:

iif(Format([ContCancDate]-[ContStartDate],"yy"" yrs ""mm"" mths""")="99 yrs 12 mths", "0 mths",Format([ContCancDate]-[ContStartDate],"yy"" yrs ""mm"" mths"""))