Excel Formula/Function to find total number of non-overlapping months

**akynyemi** · 09-04-2015, 10:54 AM

Hi Guys, good day to you all. I have 10 projects, each with a start and an end date. I need to find the number of months in between each project's start date and end date without counting overlapping months. For example;

Project one, I count three months from Jan 2015 - Mar 2015
If project two runs from Jan 2015 - Jun 2015, I wont be counting 6 months but 3 months as I wont count the overlapping months.

I have attached an excel which has a sample data.

Thanks.

**Glenn Kennedy** · 09-05-2015, 01:06 AM

For the examples posted in your Excel sheet, what are the expected answers? I got a bit confused when trying to figure it out!!

**akynyemi** · 09-05-2015, 04:50 AM

Thanks Glenn for taking out time to check this out. I have updated the excel with appropriate examples and explanations.

Thanks again.

Regards.

**FlameRetired** · 09-06-2015, 07:21 PM

I came up with 13 months in Project 10. My attempt to check this in cells F12:G12 appear to confirm that it is 13 months. Please check my work.

The attached starts with this in E3

Formula:

Please Login or Register  to view this content.

Then array-enter this in E4 and fill down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

**FlameRetired** · 09-06-2015, 09:10 PM

Another way. This one does return 12 for Project 10.

Again in E3

Formula:

Please Login or Register  to view this content.

Then array-entered / filled down this one in E4

Formula:

Please Login or Register  to view this content.

I put these in column H.

Late Edit: The formula in my previous post has an error. I forgot to exclude the first day of the month in column D. So disregard that one. My apologies.

**akynyemi** · 09-07-2015, 09:01 AM

Hi FlameRetired,

I'm awed and dont know what to say as my request seemed impossible but you solved it.

Thanks. You're a great guy.

**FlameRetired** · 09-07-2015, 06:15 PM

You're welcome, akynyemi. It was my pleasure .... and I must admit this one was more challenging than it first appeared and great fun! Thanks for posting. Thanks for the kind words, and thanks for the rep.

Excel Formula/Function to find total number of non-overlapping months

LinkBack

Thread Tools

Rate This Thread

Display

Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Re: Excel Formula/Function to find total number of non-overlapping months

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Please help to Find the last 6 Months Total Qty from Monthly Data (SUMPRODUCT FUNCTION)

Require a formula for displaying total number of months as years with months (YY,MM)

[SOLVED] Formula to Find Total Number of Different Value Variations

[SOLVED] Use Function to find total number of times a word is used on multiple worksheets.......

Counting and summing the total number of overlapping days in two periods

[SOLVED] function string to find non-overlapping ranges of values

[SOLVED] a formula to find total compound amount after given number of yea.

Bookmarks

Bookmarks

Posting Permissions