Need a formula to add bonuses based on calendar dates

[KC2MF]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Im trying to add bonuses based on whether an event meets a certain window of time in a regular calendar year (Jan-Dec). What I'm hoping to do is have a one-in-all formula that I can apply to all the fields based on there occurrence. For instance we have . . .

Group 1: 3 Shares (Occurs on 01/03/2011)
Group 2: 7 Shares (Occurs on 05/31/2011)
Group 3: 18 Shares (Occurs on 07/07/2011)
Group 4: 4 Shares (Occurs on 09/30/2011)
Group 5: 10 Shares (Occurs on 12/28/2011)

And the criteria for placing bonuses or maturities on these groups based on their event timing is say . . .

Period 1: 01/01/2011 - 01/31/2011 (no bonus awarded)
Period 2: 02/01/2011 - 03/31/2011 (a 5% bonus in shares is awarded)
Period 3: 04/01/2011 - 06/30/2011 (a 10% bonus in shares is awarded)
Period 4: 07/01/2011 - 12/31/2011 (a 15% bonus in shares is awarded)

Beginning and End dates are included in the period time-frames. So the add-on would be a multiple of the shares they already have. Group 5 has 10 shares and occurs in Period 4 therefore they get an additional 1.5 shares for their late date in maturity.

 

[duboost]

Would vlookup work? I'm not sure how you have your data arranged, but for example:

In A2:A5 list the start dates for each period.
In B2:B5 list the Bonus %
This will be your lookup table

....A.............B
Start Date...Bonus
1/1/2011......0%
2/1/2011......5%
4/1/2011......10%
7/1/2011......15%


Then if you had your group data in D1:F6, for example:

....D............E..............F
Group #.....Shares.......Date
Group 1........3..........1/3/2011
Group 2........7..........5/31/2011
Group 3.......18..........7/7/2011
Group 4........4..........9/30/2011
Group 5........10........12/28/2011

Then in G2 you can put this formula to calculate the bonus for group 1 and drag down to calculate for each group after that:

=(VLOOKUP(F2,$A$2:$B$5,2))*E2

Then of course if you wanted to calculate the total with the bonus it'd be this (and drag down)

=((VLOOKUP(F2,$A$2:$B$5,2))*E2)+E2

Hope that helps