Calculate number of months in year

[littlelax]

Hi,

I would like to calculate the number of months in a period such as 2012 dependant on a start and end date. The attached spreadsheet has an example of this but basically i need:

Start Date = 01/08/2011
End Date = 31/05/2012
Calc should be = No. of months in 2012 = 5

I need this to work for any start and end date criteria.

As always many thanks for your help

[Fotis1991]

Hi

Pls can you explain me, why 01/01/12---31/12/12=12

and 01/05/12---31/08/12= 3 ??

I think that or first result must be 11, or second result, must be 4!!

Is it corect??

[littlelax]

Hi Fotis1991,

Apologies, you are correct the 01/01/2012-31/12/2012 should show 11

[tom1977]

hi please check attachment

[Fotis1991]

..So in D5

=MONTH(B5)-MONTH(A5)

Hope to helps you

[Pete_UK]

This seems to do what you want:

=IF(B5>DATE(2012,12,31),12,IF(B5<DATE(2012,1,1),0,MONTH(B5)))-IF(A5>DATE(2012,12,31),0,IF(A5<DATE(2012,1,1),0,MONTH(A5)-1))

although it returns 4 for your third example (1/05/2012 to 31/08/2012) instead of 3. However, if you count the months in that range you will find that there are 4. The formula will count part months as whole months, so 31/01/2012 to 1/03/2012 will return 3.

Hope this helps.

Pete

[daddylonglegs]

Try this formula

=IFERROR(DATEDIF(MAX(DATE(2012,1,0),A5),MIN(DATE(2013,1,0),B5),"m"),0)

[asaad103]

use this sample function

=DATEDIF(A5,B5,"m")