Calculate Between Dates to get month and summarize to options

Bearcatrunner

I have locations that have various future leases that I would like to summarize into one column.  So I have option dates and I would like to calculate the number of months from the first option date to the second one and then the second one to the third one and so forth up to 15 potential option dates.  So for the table below.  The first location (1) I would only have one option which is for 60 months so I would like it to display like what you see in the [Future Option] column (3/1/209 to 3/1/2034 = 60 months)  So 1 option for 60 months or 1 X 60.  

For location 2 I have two remaining options.  (1/1/2028 to 3/1/2033 = 62 months), (3/1/2033 to 3/1/2038 = 60 months).  Example data loaded.  

Table below only to try and show my manual work and how I got to the future years. 

Lease Data for example 2.xlsx

jrlindem

@Bearcatrunner Interesting problem you've got.  Please see the attached workflow and screen shot.  There's likely some refinement that could occur, but splitting things out helps make the solution more illustrative for you.

The essence of what you are after is using Multi-Row Formula tools and then Transpose/Cross-Tab'ing with some added formula's to add the descriptive text:

Hope that helps, please mark as solution if this solved it for you, -Jay

Solution_Example.yxzp