Forum Discussion
Solved
Excel Formula to calculate month between dates in different years
Hi Excel Community,
I consider myself an advanced user of Excel but evern this formula has me a little stumped. I have a data sheet with various lines of contracts with varying contract term dates (start and end). Three different scenarios - start in 2016 and end in 2017, start in 2017 and end in 2017, start in 2017 and end in 2018.
I need to calculate the number of 2017 months in all scenarios. I think my formula needs to be like this:
If start date year does not equal 2017 and end date year equals 2017 then calculate # of months end date has in 2017.
Otherwise, if start date year does equal 2017 and end date equals 2018 then calculate # of months start date has left in 2017.
Here is the formula I started building but it is not giving me what I am looking for:
=(YEAR(P2)-YEAR("1/1/17"))*12+MONTH(P2)-MONTH("1/1/17")
Here is a screeshot sample of data:
Any ideas or help will be greatly appreciated.
Thanks,
Dominica Smith
Thanks for the quick response, however, how do we get it to simply count the months instead of full months?
Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.
Hi,
The formula you typed isn't valid!
You have to use something like this:
=IF(AND(YEAR(A1)=2017,YEAR(B1)=2017), DATEDIF(A1-1,B1+1,"m"), IF(AND(YEAR(A1)<>2017,YEAR(B1)=2017), DATEDIF(DATE(2017,1,1)-1,B1,"m"), IF(AND(YEAR(A1)=2017,YEAR(B1)<>2017), DATEDIF(A1,DATE(2017,12,31)+1,"m"), IF(AND(YEAR(A1)<>2017,YEAR(B1)<>2017), 0, ))))
But please note that this formula counts only the completed months in 2017.
Please see the attached workbook to test the formula.
Regards,
Thanks for the quick response, however, how do we get it to simply count the months instead of full months?
Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.
- Figured it out!!!
=DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+1
