Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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$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. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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>($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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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+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.
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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.)
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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