0

I am making a leasing table where I would like to be able to enter a start date and number of months in a term. I would like the table to return the data in a format that begins with the term start date in 12 month increments for example 120 months (10 years) and then stop. For instance if I entered 60 months, I would want the table to end at 5 years. How can I format this? TIA!

Example of how I want the table to look

1
  • Please make the effort to show the table without using the image. Aug 6 at 0:04

2 Answers 2

1

You can enter the following formulas, and copy them down from K3, L3, and N3 to whatever number of rows match the maximum number of years you want to provide for. So, for a maximum lease length of 40 years (perhaps a land lease), down to row 42.

Column K:

=IF((ROW()-2)*12<=$N$1,"Year "&(ROW()-2),"")

Column L:

=IF((ROW()-3)*12<$N$1,EDATE($L$1,(ROW()-3)*12),"")

Column N:

=IFERROR(EDATE(L3,12)-1,"")

The key to your dates is the EDATE function. Since you start with the start of a month, just that gets a year's "n-th" of the month starting date. To get the "n-th minus one day" of the month ending date, just subtract one from its result.

As a note on your image, not the general problem, the year end date for a year beginning on the first of a month is the last day of the month preceding it. (For the following year, of course.) Your use of "10/31/2023" is actually a 13 month "year" not 12. If that was intentional, you should change the text of your question. It would also require a slight change to the formulas.

You could make the formulas more user-friendly by creating Named Ranges for the starting date and the term. Or use LET to do essentially the same, but keep all your components immediately available inside each formula.

If you have SPILL functionality available, you can use these three formulas instead:

Column K:

=IF((ROW(L3:L42)-2)*12<=$N$1,"Year "&(ROW(L3:L42)-2),"")

Column L:

=IF((ROW(L3:L42)-3)*12<$N$1,EDATE($L$1,(ROW(L3:L42)-3)*12),"")

Column N:

=IFERROR(EDATE(L3,12*(ROW(L3:L42)-2))-1,"")

which avoids the need to copy and paste, or to remember to do so after updates and upgrades to your formulas.

New contributor
Jeorje is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Further to the previous answer, if you are an O365 user that can use arrays, these three formulae for Columns K, L, and N do it all and spill as needed.

=MAKEARRAY(O1,1,LAMBDA(r,c,"Year "&r))
=MAKEARRAY(O1,1,LAMBDA(r,c,EDATE(L1,(r-1)*12)))
=MAKEARRAY(O1,1,LAMBDA(r,c,EOMONTH(EDATE(L1,(r-1)*12),11)))

If you want to be a show-off you can create the whole table with one single formula instead of 3 by using a choose that varies by columns 1-3:

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,"Year "&r,EDATE(L1,(r-1)*12),EOMONTH(EDATE(L1,(r-1)*12),11))))

If so you may want to use Alt-Enters to break it into something slightly more readable like:

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,
"Year "&r,
EDATE(L1,(r-1)*12),
EOMONTH(EDATE(L1,(r-1)*12),11)
)))

Also, sometimes with spilling it's easier to force a date format right in your formula in case your arrays spill into new territory, so you could place the results of the 2nd and 3rd formula in a text( result, "mm/dd/yyyy") function:

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,
"Year "&r,
TEXT(EDATE(L1,(r-1)*12),"mm/dd/yyy"),
TEXT(EOMONTH(EDATE(L1,(r-1)*12),11),"mm/dd/yyyy")
)))

OK, really, I'm done now, LOL.

New contributor
Max is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.