How do I calculate the number of months between two dates in Excel? [duplicate]

I have two columns on Microsoft Excel: F is the column with the date service ended; E is the column with the date service began.

How do I calculate and report the number of months served for each person?


DATEDIF function explicitly gives you the number of months between two dates - it rounds down

=DATEDIF(A2,B2,"m")

where A2 is the earlier of the two dates

If you want to round to the "nearest" whole month you can use this version

=DATEDIF(A2,B2+15,"m")

If you don't care about the day and any date in Jan to any date in March is 3 months then you can use this version

=DATEDIF(A2-DAY(A2)+1,B2,"m")+1


There is a KB from Microsoft covering this very topic here

It provides two solutions, depending if you want to round the months down or up.

Round up:

=(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

Round down:

=IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

In both cases EDate is the cell containing the earlier date, and LDate is the cell containing the later.