Calculating how many months are within several time periods

Hello all,

I'm new to the forum, so I apologize if I make any mistakes here.

I'm working with Excel 2007. I've got a spreadsheet that I am using to calculate how much time (in months) is within several time periods.

I have up to ten sets of dates. A beginning date and an end date. I want to total all those date sets then determine how much time total (without double counting overlapping periods) is within a year, how much time is within 3 years, and how much time is within 5 years.

For example:

In one date set I have a beginning date of 4/1/2010 and an end date of 3/15/2014. I know that there is roughly 10.50 months within the last year there. In order to reach that conclusion I use this formula:

=(IF((-(DAYS360(D7,B7)/30))>=12,12,F7))+((DAYS360(TODAY(),D7)/30))

B7 is where the beginning date is located (4/1/2010). D7 is where the end date is located (3/15/2014). F7 is where the total months are calculated.

I use 30 day months and 360 day years to calculate all of this info.

Using this same date set, I also know that there are roughly 34.50 months within the last 3 years using this formula:

=IF(B7<=(TODAY()-(365*3)),36,F7)+((DAYS360(TODAY(),D7)/30))

Again, I know that there are roughly 45.97 months within the last 5 years using this formula:

=IF(B7<=(TODAY()-(365*5)),60,F7)+((DAYS360(TODAY(),D7)/30))

These formulas all work individually for one date set. However, when I try to calculate all ten date sets that I might have, the overlaps double up the numbers. I end up with like 15 months in the last year, which is obviously impossible.

I tried calculating them all together individually then just SUMing them up in sections titled "In the last year" etc, but that didn't work because of overlapping dates.

What I am needing to figure out is:

1.) A way to streamline these formulas if possible. They seem verbose. I figure there is some function that I don't know about that would help immensely, but I'm not very advanced in excel.

2.) A way to only count overlapping dates one time so that it does not show too many months within a year, within 3 years, and within 5 years.

3.) A way to add all the date sets together efficiently, taking into account #2.

Any help would be greatly appreciated!

Attach a sample workbook, not a picture of your workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic