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.

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.

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.

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.

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!

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").

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.