I was looking for some help with a formula. I want to count the number of months between two dates and have them be separated by the year. I received a formula but all it did was count the months and put them in the last year column. The formula that I am using is below. It works in my test sample when I type in the dates but not when I pull them from our data source.
=IFERROR(DATEDIF(MAX($F2,DATE(K$1-1,12,31)),MIN($G2,DATE(K$1,12,31)),"m")+1,0)
Column F Column H Column I
(End Date) (Begin Date) (Number of months Counted) 2011 2012 2013 2014 2015 2016
[TABLE="width: 1580"]
<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]3/1/2019[/TD]
[TD]09/1/2018[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]08/1/2018[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]08/1/2018[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]07/1/2018[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]06/1/2018[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]04/1/2018[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]02/1/2018[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]06/1/2016[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]11/1/2018[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]11/1/2018[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
So I need a formula that with count the number of months in years 2011 - 2019. Hopefully the formula will state zeros for the years with no months and the years that the dates have months just compute those months. But as you can see the 2019 column has a higher number then it should be and the other years are not calculating.
Does that make sense? Is there any help out there
=IFERROR(DATEDIF(MAX($F2,DATE(K$1-1,12,31)),MIN($G2,DATE(K$1,12,31)),"m")+1,0)
Column F Column H Column I
(End Date) (Begin Date) (Number of months Counted) 2011 2012 2013 2014 2015 2016
[TABLE="width: 1580"]
<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]3/1/2019[/TD]
[TD]09/1/2018[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]08/1/2018[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]08/1/2018[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]07/1/2018[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]06/1/2018[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]04/1/2018[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]02/1/2018[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]06/1/2016[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]11/1/2018[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]11/1/2018[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
So I need a formula that with count the number of months in years 2011 - 2019. Hopefully the formula will state zeros for the years with no months and the years that the dates have months just compute those months. But as you can see the 2019 column has a higher number then it should be and the other years are not calculating.
Does that make sense? Is there any help out there