Search code examples
exceldatedate-difference

Excel: get months between start and end date in given year or months through end date if start is in a prior year

A brief background: I work in the insurance industry, and for various tax reasons, we often need to know how many months in a year the employee was employeed, and how many months they were offered insurance coverage. An employee becomes eligible for insurance on the first of the month after 60 days of employment.

Getting the date of eligibility was simple. Technically, getting the number of months employed and offered is simple... Until I get to situations that span multiple years. For the purposes of my report, I only need to know the data for 2017. For instance, R3 is only employed two months in 2017, so I would need "#Mon Emp" to be 2. I'm currently using =DATEDIF(F2,H2+15,"m"), but that gives me the full 6 months.

Then, I also run into a problem if there is no value in "Term Date" (i.e. they are still employed). I know there must be a formula that can help me, but I'm not overly knowledgeable in Excel formulas, and I couldn't find something similar in any other questions. I can't just change the hire date to 1/1/2017, because that messes up the eligibility.

Data set up

Thanks so much for any and all help!

Solution

  • Here is an approach to what you are looking for assuming the following:

    • "Date of Hire" is column F and is always the earliest date
    • "Date of Eligibility" is column G and is always earlier than "Term Date"
    • "Term Date" is column H and is either blank or a date during the year 2017
    • Formulas below output the number of complete months employed during 2017, you should be able to translate these fairly easily for the number of months benefits were offered during that year (examples below show formulas for data in row 2)

    The formula...

    IF(ISBLANK(H2),IF(YEAR(F2)=2017,DATEDIF(F2,DATE(2018,1,1),"m"),12),IF(YEAR(F2)=YEAR(H2),DATEDIF(F2,H2,"m"),DATEDIF(DATE(YEAR(H2)-1,12,31),H2,"m")))

    Breaking it down...

    If "Term Date" is empty: ISBLANK(H2), then get number of months employed through end of 2017.

    Formula section to get number of months employed through end of 2017: IF(YEAR(F2)=2017,DATEDIF(F2,DATE(2018,1,1),"m"),12)

    If "Date of Hire" was during 2017: YEAR(F2)=2017, then get number of months from "Date of Hire" through end of 2017: DATEDIF(F2,DATE(2018,1,1),"m"), otherwise assume "Date of Hire" is before 2017 resulting in 12 months: 12.

    If "Term Date" is not empty, then get the number of months employed in 2017 through the "Term Date".

    Formula section to get the number of months employed in 2017 through the "Term Date":

    IF(YEAR(F2)=YEAR(H2),DATEDIF(F2,H2,"m"),DATEDIF(DATE(YEAR(H2)-1,12,31),H2,"m"))

    If "Date of Hire" and "Term Date" are the same: YEAR(F2)=YEAR(H2), then get the difference between the dates: DATEDIF(F2,H2,"m"), otherwise get the difference between Dec 31st of the prior year and the "Term Date": DATEDIF(DATE(YEAR(H2)-1,12,31),H2,"m").

    Important note...

    You could update the formula to work for any calculation year by replacing the 2017 and DATE(2018,1,1) in the formula section that gets the number of months employed through the end of 2017 with references to cells where you can input the relevant values in order to run the calculations for different years.