Calculate number of months for a specific year between 2 cells with dates in them

Hello All,

I have a spreadsheet for multiple prepaid paid services that i will need to expense over the begin/end date of the expense. I have a cell where it calculates the number of months the service will need to be expensed. What I am trying to do is create a formula to count the number of months for a specific year below that falls between the begin date(Cell) and end date (Cell)... I will then multiply the number times the actual monthly expense.

I have been doing this manually and I know this may be more complex to have automated. Any assistance would be greatly appreciated!


Original Amount 2015 2016 2017 Total Amt Begin Date End Date # of Months
9,209.00 ??????? ?????? ?????? 9,209.00 1/2/2015 7/5/2017 31

Calculate months between 2 dates:

=DATEDIF(MIN(A1:B1),MAX(A1:B1),"m")&" months "

I appreciate your reply Azumi. I have that formula to calculate the number of months for the whole duration.

My question is how do I count the number of months that are only for 2017 that falls in between two different cells that contains the date "1/2/2015" (cell F2) and "7/5/2017" (cell G2)

Please see this and hope the best

Thanks. How do I open the file. I am new here and it keeps telling me to register to open it even though I am already logged in

Ok Im write here :

=IF(OR(VALUE(F5)<YEAR(F2),VALUE(F5)>YEAR(G2)),"",INT(MOD((ABS(IF(DATE(F5,1,1)>F2,DATE(F5,1,1),F2)-IF(DATE(F5,12,31)<G2,DATE(F5,12,31),G2)))/365,25,1)*12)&" months")

F2=Start Date
G2=End Date

and

F5=Year Criteria

I have entered the formula you gave and received the too many arguments error and do not see anything that stick out to being misplaced. Below are the values that i have for my formula. I believe we are close and was wandering if i could get your help one more time.. The answer im looking for should be "8" months that this "service" would be expensed in 2015

=IF(OR(VALUE(F2)<YEAR(N3),VALUE(F2)>YEAR(O3)),"",INT(MOD((ABS(IF(DATE(F2,1,1)>N3,DATE(F2,1,1),N3)-IF(DATE(F2,12,31)<O3,DATE(F2,12,31),O3)))/365,25,1)*12)&" months")


F2=2015
N3= 5/1/2015
O3= 7/1/2017

Can you download my file on post #4?

or try this link:
https://www.dropbox.com/s/nks4h9q6qn...nths.xlsx?dl=0

Your the man... Works perfect. I just had to manually add "1" to include the starting month when the formula counts the number of months in between.

Thank you.

thanks for the feedback

One last question and I believe we are 95% there... Is there a way to ignore the the "day" in the date... All I need is the number of months and dont want to account for what day it started..... having the specific day there is causing some calculations to be shorter/longer than they should.

=IF(OR(VALUE(N$1)<YEAR($V5),VALUE(N$1)>YEAR($W5)),"0",INT(MOD((ABS(IF(DATE(N$1,1,1)>$V5,DATE(N$1,1,1),$V5)-IF(DATE(N$1,12,31)<$W5,DATE(N$1,12,31),$W5)))/365.25,1)*12))

Thank you. You guys are awesome!