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.

closed as off-topic by fixer1234, DavidPostill, mdpc, Kevin Panko, bwDraco Apr 19 '15 at 4:49

  • This question does not appear to be about computer software or computer hardware within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Can 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 Apr 9 '15 at 4:45
  • 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 Apr 9 '15 at 4:49
  • 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 Apr 9 '15 at 6:58
  • 2
    I'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 Apr 15 '15 at 23:17
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)))

  • 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 Apr 12 '15 at 23:22
  • In this case EOMONTH needs to be used as well. – sicarius92 Apr 15 '15 at 22:43
  • 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 Apr 15 '15 at 23:25
  • 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 Apr 17 '15 at 22:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.