No of months between 2 different years by year

hi all,

I need your help in solving the below issue.

I have two dates in two different years. I would like to know the number of months in between the dates by year. I would explain it with an example for better understanding.

Following are the dates that I have - 1/1/2012 & 3/2/2013.

I can calculate the number of months between these two dates; but what I want is the numbers of months in 2012(ie - 12months) & and the number of months in 2013(3 months).


Appreciate your help in this regard as this is really urgent

A1 = Start Date
B1 = End Date

C1 = 13 - Months(A1)
//number of months in the starting year

D1 = MAX(( Year(B1) - Year(A1) - 1)* 12, 0)
//number of months in the years in between start year and in year (exclusive of start and end years)

E1 = Months (B1)
//number of months in end year.

Note this will treat Jan 1st and Jan 30th as being exactly the same for the purpose of counting months.

thanks Ben. thats works...

Appreciate your timely help.

Regards
akila

Hi Ben,

I have one more query.

How can work this formula if there are more than 2 years. For eg - start date is 1/1/2011; end date is 2/1/2013. When I use the above formula i miss the months in 2012.

Appreciate your help in this regard.

Regards
Akila

hi Alkey,

thanks for your reply. However what I want is the number of months in each year between the dates.

Appreciate your help.

Regards
Akila

Originally Posted by AkilaAnnamalai

How can work this formula if there are more than 2 years. For eg - start date is 1/1/2011; end date is 2/1/2013. When I use the above formula i miss the months in 2012.

Originally Posted by ben_hensel

D1 = MAX(( Year(B1) - Year(A1) - 1)* 12, 0)
//number of months in the years in between start year and in year (exclusive of start and end years)

If you want the months per year you're going to have to set up a table first like the little spider said above me.

Thanks all. Your help is much appreciated.

Regards
Akila