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.