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.
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
– Scott – 2015-04-09T06:58:45.883=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.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