Calculating #of Months YTD within a date range

[May2010]

Hi,

I am trying to calculate the number of months in the current year that have passed within a specific date range. I would like this formula to work regardless of the current year (i.e., I don't want to hardcode 2010).

Example: The date range is Dec 2009 - Nov 2010, the current year is 2010, so the number of months in that range that are in 2010 and have ended and begun is 10 (Jan 2010, Feb 2010, Mar 2010, April 2010, May 2010, June 2010, July 2010, August 2010, Sep 2010, Oct 2010).

Here is my sample data:

<TABLE style="WIDTH: 145pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=193 border=0 x:str><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: white" width=65 height=34>

Begin​

</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>

End​

</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=64>

Expected Result​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17 x:num="40148">

Dec-09​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num="40483">

Nov-10​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>

10​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17 x:num="40299">

May-10​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num="40634">

Apr-11​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>

6​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17 x:num="40339">

Jun-10​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num="40664">

May-11​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>

5​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=65 height=17 x:num="40431">

Sep-10​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num="40756">

Aug-11​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>

2​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40544">

Jan-11​

</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40878">

Dec-11​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

0​

</TD></TR></TBODY></TABLE>

Thank you!!!!

 

[rob.barnes01]

Try:

=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

 

[Foo_Man_Chu]

ok, this isn't the prettiest formula ever, but i believe that it does what you are wanting it to:

=IF(AND(YEAR(A10)<YEAR(NOW()),YEAR(B10)=YEAR(NOW())),MONTH(B10)-1,IF(AND(YEAR(A10)=YEAR(NOW()),YEAR(B10)=YEAR(NOW())),MONTH(B10)-MONTH(A10)-1,IF(AND(YEAR(A10)=YEAR(NOW()),YEAR(B10)>YEAR(NOW())),12-MONTH(A10),0)))

by the way, i think your examples are inconsistent. the months in 2010 between may 2010 and april 2011 would be 7, not 6 (june 2010, july 2010, august 2010, september 2010, october 2010, november 2010 and december 2010).

hope this helps.

 

[May2010]

Foo_Man_Chu, I haven't been able to make your formula work yet, but what I'd like to count are the months in 2010 that have passed (including the current month), so from May 2010 - April 2011 the count would be 6 (May 2010, Jun 2010, Jul 2010, Aug 2010, Sep 2010, Oct 2010) = 6. Thank you.

 

[May2010]

rob.barnes01: I tried using your formula, but all the rows came out to 11. I verified each formula on each row was linked to the correct cells. Still trying to solve this one... Thank you!

 

[Foo_Man_Chu]

oh, i wasn't aware you actually wanted to use the two dates in the table in conjunction with the actual month of today. i don't have time right now to figure this out, but i'll look into it later on today and i'll figure it out, sorry to make you wait, but if no one else get's you an answer before i get to it i'll make sure to help you out.

 

[shg]

Here's mine:

      ----A----- ----B----- --C--- -------------------D-------------------
  1     Begin       End     Result Note                                   
  2   1-Dec-2009 1-Nov-2010     10 Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sep/Oct
  3   1-May-2010 1-Apr-2011      8 May/Jun/Jul/Aug/Sep/Oct/Nov/Dec        
  4   1-Jun-2010 1-May-2011      7 Jun/Jul/Aug/Sep/Oct/Nov/Dec            
  5   1-Sep-2010 1-Aug-2011      4 Sep/Oct/Nov/Dec                        
  6   1-Jan-2011 1-Dec-2011      0

The formula in C2 is

=DATEDIF(MAX(A2, "1/1/" & YEAR(TODAY())), MIN(B2, "1/1/" & (YEAR(TODAY())+1)), "m")

 

[May2010]

shg4421: This formula is almost there, but it includes November 2010 - December 2010 in the count in rows 3-5. I only want to count the months until the current month (October 2010). Thanks

Foo_Man_Chu: Thank you! Hopefully I can figure this out so you can use your time for better things

 

[May2010]

The expected results are the actual results I should get from the formula:

<table style="width: 145pt; border-collapse: collapse;" width="193" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 25.5pt;" height="34"><td class="xl23" style="border: 0.5pt solid windowtext; width: 49pt; height: 25.5pt; background-color: white;" width="65" height="34">

Begin​

</td><td class="xl23" style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 48pt; background-color: white;" width="64">

End​

</td><td class="xl27" style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 48pt; background-color: rgb(255, 255, 153);" width="64">

Expected Result​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; width: 49pt; height: 12.75pt; background-color: transparent;" width="65" height="17">

Dec-09​

</td><td class="xl22" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

Nov-10​

</td><td class="xl24" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

10​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; width: 49pt; height: 12.75pt; background-color: transparent;" width="65" height="17">

May-10​

</td><td class="xl22" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

Apr-11​

</td><td class="xl24" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

6​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; width: 49pt; height: 12.75pt; background-color: transparent;" width="65" height="17">

Jun-10​

</td><td class="xl22" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

May-11​

</td><td class="xl24" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

5​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; width: 49pt; height: 12.75pt; background-color: transparent;" width="65" height="17">

Sep-10​

</td><td class="xl22" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

Aug-11​

</td><td class="xl24" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" width="64">

2​

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl25" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12.75pt; background-color: transparent;" height="17">

Jan-11​

</td><td class="xl25" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">

Dec-11​

</td><td class="xl26" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">

0​

</td></tr></tbody></table>

 

[shg]

=IFERROR(DATEDIF(MAX(A2, "1/1/" & YEAR(TODAY())), MIN(B2, EOMONTH(TODAY(), 0) + 1), "m"), 0)