# Thread: Lease expiry dates (2003 sp2)

1. ## Lease expiry dates (2003 sp2)

Good morning

If I have a date in say cell A1 (lease start date) and a number in B1 (Lease months) how can I multiply A1 and B1 to return a date in C1 that is, for the purposes of my requirment, 33 months on from the date in cell A1

Thank you

Steve

2. ## Re: Lease expiry dates (2003 sp2)

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

Steve

3. ## Re: Lease expiry dates (2003 sp2)

Thanks Steve, that is great

Steve

4. ## Re: Lease expiry dates (2003 sp2)

Steve

I would just offer a word of caution on this - the formula works fine so long as the day of the lease start date is not near the end of the month. If it is on say the 30th or 31st of the month you may get unexpected results.

For example using the formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) where A1 is Jan 30th, and B1 is 1 gives the result of March 2nd. This may not be what you wanted.

I'm not sure of the best way to get round this if you need to.

Nick

5. ## Re: Lease expiry dates (2003 sp2)

If you activate the Analysis ToolPak add-in, you can use the EDATE function:

=EDATE(A1,B1)

If A1 = January 30, 2007 and B1 = 1, the result of the formula will be February 28, 2007.

6. ## Re: Lease expiry dates (2003 sp2)

Hans

That is exactly what I was looking for to use in another worksheet. Your knowledge is, as usual, spot on.

Thanks

Nick

7. ## Re: Lease expiry dates (2003 sp2)

Yes this will never happen if the day of the month is <29.

It will happen when the day of the current month has more days than the future month. If the future month has less days then the future date will be 1-3 days later than the last day of the future month (depending on how many days difference)

If that is a concern and if the date should never be beyond the last day of the "B1"th month then you could use:
=MIN(DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1) ,MONTH(A1)+B1,DAY(A1)))

This formula will yield (for example) if B1 is 1 month, from Jan 1 - 28 will yield Feb 1-28 (respectively) and Jan 29-31 will all yield Feb 28.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•