Calculating the term year of rows of dates

[mikeniedert]

I've been working on this for hours and haven't been able to figure it out. I'm hoping one of you smart people can help.

I created a spreadsheet to help me calculate costs of some of our long term contracts. There is one part of this spreadsheet that is causing me more grief than I thought. On my spreadsheet, I have a number of variables (Start Date, Term Length, etc.) Once I input the Term Length (in cell D3) and the Start Date (in cell D5), it will display the start and end dates for each month of the term. So for example, if I have a contract with a 24-month term (which I input in D3) and it starts on 8/1/2017 (which I input in D5), it will show in Cell B11 a number "1", which means the first month of the contract...and in C11 it show the 8/1/2017 start date, and in D11 it calculates the end date on the first month of the contract (in this example 8/31/2017). The next row shows the information for the second month of the contract (i.e., 2, 9/1/2017, 9/30/2017) and so on until the last row displays (24 in B34, 7/1/2019 in C34, and 7/31/2019 in D34).

What I need is a formula for Column A, which would calculate the term year that each of those rows belong to. So Rows 11-22 would each display a "1" meaning the first year of the contract...and the next 12 rows (rows 23-34) would show "2" for the second year of the contract.

Thank you in advance for your smartness

 

[Eric W]

Welcome to the board.

Put this in A11 and drag down:

=IF(B11="","",INT((ROWS($A$11:$A11)-1)/12)+1)