How to count months in intersection of two date ranges

1

I need to calculate the number of months in the intersection of two date ranges.

For instance, in Column A I have start dates of employment 

12/1/1998, 1/1/2003, 2/1/1995

and in column B I have end dates of employment, ie. 

 12/31/2005, 2/28/2009, and still employed.

I need to calculate the number of months each employee was employed from 01/01/2000 to 05/01/2006 (total of 76, maximum).

Is there a formula I can use to calculate only the relevant period in months?

I use Excel 2013.

Jessica Feeney

Posted 2015-04-09T04:18:13.977

Reputation: 11

Question was closed 2015-04-19T04:49:08.783

1Can we assume the comma-separated dates in columns A and B are on separate rows? When you refer to "each employee", are you referring to one row? So for each row, you're looking for what portion, if any, of the employment period fell within that range? What's the definition of a month (calendar month, 30 days)? How do you handle fractional months (round up, round down, include fraction, fraction of the actual month, fraction of 30 days)? Would there be a problem replacing all "still employed" with a date (could be TODAY() for calculations)? – fixer1234 – 2015-04-09T04:45:40.863

Yes Column A has each date seperated by a comma in a different row and Column B is the same as far as the dates. Yes I could replace the date with today – Jessica Feeney – 2015-04-09T04:49:05.317

You need to make your question clearer … but, look at =MAX(A1,DATE(2000,1,1)) and =MIN(B1, DATE(2006,5,1)).  Determining the number of months between those two dates might be what you want.  And see How to calculate age in years? for ideas on how to determine the difference between two dates, and How to divide a time period into equal time periods for other considerations.

– Scott – 2015-04-09T06:58:45.883

2I'm voting to close this question as overly broad because there are too many unclarified aspects, creating too many possible ways the question can be interpreted. – fixer1234 – 2015-04-15T23:17:37.833

Answers

0

I would say

=(YEAR(B1)*12+MONTH(B1))-(YEAR(A1)*12+MONTH(A1))

even though the recommended formula is EOMONTH.
Since OP didn't quite accept (or specify) anything while someone else made a remark without coming up with anything constructive here is a non-integer solution that is based on the latter month's date (as denominator, e.g. May->(DayOfMonth(latter)-DayOfMonth(former))/31). Remark: A1: date of entering, A2: day of leaving/today.

=YEAR(B1)*12+MONTH(B1)-(YEAR(A1)*12+MONTH(A1))+(((B1-EOMONTH(B1,-1))-(A1-EOMONTH(A1,-1)))/(EOMONTH(B1,0)-EOMONTH(B1,-1)))

sicarius92

Posted 2015-04-09T04:18:13.977

Reputation: 11

That isn't what the question asks. It's about the portion of that period that is within the range 1/1/2000 and 5/1/2006. Also, this doesn't really deal with fractional months. 1st of month1 to last of month 2 is nearly 2 months longer than last of month 1 to 1st of month 2. – fixer1234 – 2015-04-12T23:22:29.583

In this case EOMONTH needs to be used as well. – sicarius92 – 2015-04-15T22:43:15.093

That still doesn't address the 1/1/2000 to 5/1/2006 requirement. Regarding EOMONTH, there is no way to know if that is how the OP intended months and fractional months to be handled. In fairness to you, the question really isn't answerable in its current form because the requirements are ambiguous. – fixer1234 – 2015-04-15T23:25:26.480

As long as OP doesn't specify what he wants I will stick to my solutions which give the equivalent output to the example. – sicarius92 – 2015-04-17T22:40:55.010

Asked: 2015-04-09T04:18:13.977

Viewed: 460 times

Active: 2018-08-01T19:06:47.937