# Thread: Rolling fiscal year contract months (2003 SP3)

1. ## Re: Rolling fiscal year contract months (2003 SP3)

I came up with this for P6:

=MAX(MIN(12*P\$4-(12*\$I\$6+\$H\$6-1),(12*\$I\$6+\$H\$6+\$J\$6-1)-12*P\$4,12),0)

but it's still rather long and artificial. It can be copied to the right.

2. ## 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>(\$I6-1)</font color=red>+\$H6-1),(12*\$I6+\$H6+\$J6-1)-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>

3. ## Re: Rolling fiscal year contract months (2003 SP3)

I created intermediate formulas, for example

First month: =12*I6+H6
Last month: = 12*I6+H6+J6-1

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.

4. ## Rolling fiscal year contract months (2003 SP3)

<P ID="edit" class=small>(Edited by JohnBF on 01-Jul-08 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.

5. ## 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.)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•