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. 

[jrlindem · Accepted solution]

@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

[caltang · Accepted solution]

Like so?

[Bearcatrunner]

You both answered my question which makes me feel like such a newbie and thus I tried to accept both as the solution.  Thank-You for your help.  You are both rockstars.  

[jrlindem]

@Bearcatrunner No worries!  There are many paths to get to solutions, so marking both is appropriate if they're both relevant.  Glad it was helpful and that's what the community is for!