Count number of months per year between two dates

[BenB0425]

So I'm having trouble coming up with a formula to be able to count the number of months between two given dates and break it down by year. So if the dates are from (11/2018 until 8/2019) I would like it to break it down to 2 months for 2018 and 8 months for 2019. But I have a range of dates from 2011 until 2019. Below is a copy of the difference in months but I would like it to expand and elaborate on how many months per year were utilized. These date are just from 2017 until 2019 but I have other dates backing all the way to 2011.

[TABLE="width: 505"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]03/2019[/TD]
[TD]06/2018[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]03/2019[/TD]
[TD]05/2018[/TD]
[TD][/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]03/2019[/TD]
[TD]03/2018[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]03/2019[/TD]
[TD]07/2017[/TD]
[TD][/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone help?
Thanks

 

[alansidman]

https://www.techonthenet.com/excel/formulas/datedif.php

 

[DanteAmor]

Try:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01/11/2018</td><td style="text-align:right; ">01/08/2019</td><td style="text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01/05/2018</td><td style="text-align:right; ">01/03/2019</td><td style="text-align:right; ">11</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=DATEDIF(A2,B2,"m")+1</td></tr></table></td></tr></table>

 

[BenB0425]

Try:

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]01/08/2019[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]11[/TD]

</tbody>

Cell Formula C2 =DATEDIF(A2,B2,"m")+1 <tbody> </tbody>

<tbody>
</tbody>

I can find the difference of months between two dates but I need them to be broken down into the years that it happened.

 

[Joe4]

Welcome to the Board!

I can find the difference of months between two dates but I need them to be broken down into the years that it happened.

Where/how do you want that information broken out? Do you want to add new lines, or simply list it out in additional columns?
Is it possible that it could ever span more than 2 years?

 

[DanteAmor]

Do you need somthing like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71.29px;" /><col style="width:71.29px;" /><col style="width:19.96px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2011</td><td style="text-align:right; ">2012</td><td style="text-align:right; ">2013</td><td style="text-align:right; ">2014</td><td style="text-align:right; ">2015</td><td style="text-align:right; ">2016</td><td style="text-align:right; ">2017</td><td style="text-align:right; ">2018</td><td style="text-align:right; ">2019</td><td style="text-align:right; ">2020</td><td style="text-align:right; ">2021</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01/11/2018</td><td style="text-align:right; ">01/08/2019</td><td style="text-align:right; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01/05/2018</td><td style="text-align:right; ">01/03/2019</td><td style="text-align:right; ">11</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">8</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">01/01/2019</td><td style="text-align:right; ">05/05/2019</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table>



Or write the result as you want it on the sheet.

 

[BenB0425]

That's exactly what I'm looking for. Yes.

 

[DanteAmor]

Try

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:93.15px;" /><col style="width:93.15px;" /><col style="width:76.04px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">Start</td><td style="background-color:#ffff00; text-align:center; ">End</td><td style="background-color:#ffff00; text-align:center; ">Months</td><td style="background-color:#ffff00; text-align:center; ">2011</td><td style="background-color:#ffff00; text-align:center; ">2012</td><td style="background-color:#ffff00; text-align:center; ">2013</td><td style="background-color:#ffff00; text-align:center; ">2014</td><td style="background-color:#ffff00; text-align:center; ">2015</td><td style="background-color:#ffff00; text-align:center; ">2016</td><td style="background-color:#ffff00; text-align:center; ">2017</td><td style="background-color:#ffff00; text-align:center; ">2018</td><td style="background-color:#ffff00; text-align:center; ">2019</td><td style="background-color:#ffff00; text-align:center; ">2020</td><td style="background-color:#ffff00; text-align:center; ">2021</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01-nov-2018</td><td style="text-align:right; ">01-ago-2019</td><td style="text-align:right; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01-may-2018</td><td style="text-align:right; ">01-mar-2019</td><td style="text-align:right; ">11</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">8</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">01-ene-2019</td><td style="text-align:right; ">05-may-2019</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IFERROR(DATEDIF(MAX($A2,DATE(D$1-1,12,31)),MIN($B2,DATE(D$1,12,31)),"m")+1,0)</td></tr></table></td></tr></table>

Copy to the right and down.

 

[BenB0425]

I feel like it's close. Is it because I have the end date first and the begin date second. But it's still not calculating the right number of months. I really appreciate the help.

[TABLE="width: 1325"]
<colgroup><col><col><col><col span="8"><col></colgroup><tbody>[TR]
[TD]08/2019[/TD]
[TD]11/2018[/TD]
[TD]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08/2019[/TD]
[TD]11/2018[/TD]
[TD]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08/2019[/TD]
[TD]11/2018[/TD]
[TD]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08/2019[/TD]
[TD]10/2018[/TD]
[TD]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08/2019[/TD]
[TD]10/2018[/TD]
[TD]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08/2019[/TD]
[TD]10/2018[/TD]
[TD]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

In columns A and B you must have dates, and in row 1 of D to N you must have the year, example:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:109.31px;" /><col style="width:121.66px;" /><col style="width:76.04px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">End</td><td style="background-color:#ffff00; text-align:center; ">Start</td><td style="background-color:#ffff00; text-align:center; ">Months</td><td style="background-color:#ffff00; text-align:center; ">2011</td><td style="background-color:#ffff00; text-align:center; ">2012</td><td style="background-color:#ffff00; text-align:center; ">2013</td><td style="background-color:#ffff00; text-align:center; ">2014</td><td style="background-color:#ffff00; text-align:center; ">2015</td><td style="background-color:#ffff00; text-align:center; ">2016</td><td style="background-color:#ffff00; text-align:center; ">2017</td><td style="background-color:#ffff00; text-align:center; ">2018</td><td style="background-color:#ffff00; text-align:center; ">2019</td><td style="background-color:#ffff00; text-align:center; ">2020</td><td style="background-color:#ffff00; text-align:center; ">2021</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01-ago-2019</td><td style="text-align:right; ">01-nov-2018</td><td style="text-align:right; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01-mar-2019</td><td style="text-align:right; ">01-may-2018</td><td style="text-align:right; ">11</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">8</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">05-may-2019</td><td style="text-align:right; ">01-ene-2019</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IFERROR(DATEDIF(MAX($B2,DATE(D$1-1,12,31)),MIN($A2,DATE(D$1,12,31)),"m")+1,0)</td></tr></table></td></tr></table>