Expiration Date calculation

[Lizzy Bob]

I am creating a database to track equipment leases. I have the effective date and want to calculate the expiration date. The lease term length is given as a number of months and this is what I want to add to the effective date to arrive at the expiration date. I would appreciate any help you can offer. Thanks.

[danjacoby]

TextToDate(If(Month(Date) + NoOfMonths > 12, ((Month(Date) + NoOfMonths)) - 12 & "/" & Day(Date) & "/" & (Year(Date) + 1), (Month(Date) + NoOfMonths) & "/" & Day(Date) & "/" & Year(Date)))

Where "NoOfMonths" is the number of months until the end of the lease.

There may be a more elegant solution, but I'm tired, so this is what you get -- don't worry, it's been tested & it'll work.

[Lizzy Bob]

I'm obviously doing something wrong somewhere because FM is accepting the following string as an acceptable calculation, but I do not get any result showing in the Expiration Date field. Here's what I typed as the calculation:

TextToDate(If(Month(Effective Date)) + TermofLease > 12, ((Month(Effective Date) + TermofLease)) - 12 & "/" & Day(Effective Date) & "/" & (Year(Effective Date) + 1), (Month(Effective Date) + TermofLease) & "/" & Day(Effective Date) &"/" & Year(Effective Date)))

I have the field "Effective Date" defined as a date and the calculation field "Expiration date" defined as a date. The field "TermOfLease" is defined as a number.

This is all pretty new to me so I appreciate your patience.

[danjacoby]

There's apparently an extra close parenthesis after the first "Effective Date". Try this:

TextToDate(If(Month(Effective Date) + TermofLease > 12, ((Month(Effective Date) + TermofLease)) - 12 & "/" & Day(Effective Date) & "/" & (Year(Effective Date) + 1), (Month(Effective Date) + TermofLease) & "/" & Day(Effective Date) &"/" & Year(Effective Date)))

It worked for me.

BTW, if the lease can be longer than a year (i.e. 12 months), you'll need to amend this calc.

[Lizzy Bob]

Dan -- thanks for the note about adjusting for terms being longer than a year -- I should have made that clear in my initial request. Yes the terms are as long as 10 years in some cases and many lengths in between. What's the fix?

THANKS!

[danjacoby]

Create two new calc fields:

Years = Int(TermofLease / 12)

Months = Mod(TermofLease, 12)

Now change the Expiration Date calc to:

TextToDate(If(Month(Effective Date) + Months > 12, (((Month(Effective Date) + Months))) - 12 & "/" & Day(Effective Date) & "/" & ((Year(Effective Date) + Years + 1)), (Month(Effective Date) + Months) & "/" & Day(Effective Date) &"/" & (Year(Effective Date) + Years)))

The "Mod" (meaning "modulo") formula calculates the remainder after dividing the number ("TermofLease") by the divisor ("12").

[Lizzy Bob]

Dan -- THANK YOU. Your calculation works great!!!

I've just now had the opportunity to get back to working on this database. What a great way to start the weekend. THANKS AGAIN.