Results 1 to 5 of 5

20080630, 07:23 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Rolling fiscal year contract months (2003 SP3)
I came up with this for P6:
=MAX(MIN(12*P$4(12*$I$6+$H$61),(12*$I$6+$H$6+$J$61)12*P$4,12),0)
but it's still rather long and artificial. It can be copied to the right.

20080630, 10:14 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Rolling fiscal year contract months (2003 SP3)
That doesn't seem to pick up the first contract year, but this variation (in P6, copied) does:
=MAX(MIN(12*P$4(12*<font color=red>($I61)</font color=red>+$H61),(12*$I6+$H6+$J61)12*P$4,12),0)
It's also meant to copied down, so I edited my original post and your formula to turn the Row 6 references from absolute to relative.
How you figured that out logically I'll never know. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>John ... I float in liquid gardens
UTC 7ąDS

20080630, 10:44 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Rolling fiscal year contract months (2003 SP3)
Sorry about the missing year.
I created intermediate formulas, for example
First month: =12*I6+H6
Last month: = 12*I6+H6+J61
and for the years: =12*P4 etc.
Next, subtract first month from each year value and add fudge factor 13 (1 year to include first year + 1 month to count start month), subtract year value from last month, and use MIN/MAX to limit to the range 0 ... 12.
See attached workbook.

20080701, 03:08 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Rolling fiscal year contract months (2003 SP3)
<P ID="edit" class=small>(Edited by JohnBF on 01Jul08 08:08. )</P>I need to calculate the number of months applicable to a contract for each fiscal year. (There are hidden rows and columns in this layout.)
<table border=1><td></td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>P</td><td align=center>Q</td><td align=center>R</td><td align=center>S</td><td align=center>T</td><td align=center>U</td><td align=center>V</td><td align=center valign=bottom>4</td><td align=center valign=bottom>Start
Month</td><td align=center valign=bottom>Start
Year</td><td align=center valign=bottom>Contract
Months</td><td align=center valign=bottom>2007</td><td align=center valign=bottom>2008</td><td align=center valign=bottom>2009</td><td align=center valign=bottom>2010</td><td align=center valign=bottom>2011</td><td align=center valign=bottom>2012</td><td align=center valign=bottom>2013</td><td align=center valign=bottom>6</td><td align=right>7</td><td align=right>2008</td><td align=right>36</td><td align=center valign=bottom>0</td><td align=center valign=bottom>6</td><td align=center valign=bottom>12</td><td align=center valign=bottom>12</td><td align=center valign=bottom>6</td><td align=center valign=bottom>0</td><td align=center valign=bottom>0</td></table>This formula in Cell P6 and copied right works, but seems very long; any suggestions to improve?
=IF(AND(P$4>=$I6,P$4<=($I6+($H6+$J6)/12)),ROUND(YEARFRAC(MAX(DATE($I6,$H6,1),DATE(P$4,1 ,1)),MIN(DATE($I6,$H6+$J6,0),DATE(P$4,12,31)))*12, 0),)
(And yes, I plead guilty of asking for help with my work, but my date function math is a bit rusty.)
Edited: corrected a small flaw in the original formula.John ... I float in liquid gardens
UTC 7ąDS

20080701, 03:10 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Rolling fiscal year contract months (2003 SP3)
While you've been busy explaining it, I had to reverse engineer it to understand it. The idea of doing the math in months eluded me for a while. I would never have gotten to the "maximum ( minimum ( Year Start minus Contract Year Start, Year End minus Contract Year Start, 12), zero) conclusion by myself.
(And here's my test version FWIW  I changed the rows to reflect structural changes in the final document which contains confidential info.)John ... I float in liquid gardens
UTC 7ąDS