number of months between two dates

[baz0912]

Hi,

I have a long list of start dates and finish dates. See attached columns M-N. In column O I want to calculate the difference in "months" of the 2 dates. Can I do this using the VBA? I think I can use the datediff function. I want to click a button and the months to update.

Thanks for your help
Baz

[royUK]

Why use a macro, this formula should do it

=MONTH(N2)-MONTH(M2)

[MatrixMan]

You don't need VBA to do this ... just use the formula: "=MONTH(N2)-MONTH(M2)" in cell O2 and copy down (change number format to an integer).

[Andy Pope]

The datediff code would be

Please Login or Register  to view this content.

The formula would be

=(MONTH(N2)-MONTH(M2))+((YEAR(N2)-YEAR(M2))*12)

[MatrixMan]

LOL ... yes, good catch Andy! Didn't take into account the cases where the dates run over the year end It's always the simple things that bite you in the butt huh?

[baz0912]

Hi Andy,

Thanks for this but when I run the code I get a mis match type error in the formula bit.

Thanks
Harry

[daddylonglegs]

The formula would be

=(MONTH(N2)-MONTH(M2))+((YEAR(N2)-YEAR(M2))*12)

DATEDIF worksheet formula (with only 1 "F") can also give you same result, i.e.

=DATEDIF(M2-DAY(M2)+1,N2,"m")

although both that formula and the above would count 30th November to 1st December as 1 month. If you use just

=DATEDIF(M2,N2,"m") that only counts "whole months"

[Andy Pope]

Not sure what you mean by

I get a mis match type error in the formula bit.