Employee bonuses with varying dates/values

[McDan]

This is a complicated explanation.

Across row A I have a month in each cell from December 08 till July 11 with a diff month in each cell.

Then, running down row A is a series of dates between the same time listing when each of my employees began working for me. These do not all start on the first of the month

The rest of the sheet has how much each employee made each month, starting from the month that they began. The employees do not make the same amount every month. I would like to add a bonus of 20% to each employee for the first 12 months they have been around and then 10% for each subsequent month. How can I do this?

Start date Dec 08 Jan 09 Feb 09
3/1/2009 565 987 763
4/1/2009 738 475 595
4/28/2009 865 234 943
5/1/2009 383 634 834
6/1/2009 639 684 945

 

[MikeWx]

You can change A2 through AG2 to 12/01/2008 through 07/01/11, then reformat them as "mmm yy" so they appear as they do in your example. The following formula will then calculate 20% of the first 12 months and 10% of any remaining months for the first employee. Copy it down for the others.

=(SUMPRODUCT(--($B$1:$AG$1>=EOMONTH(A2,-1)+1),--($B$1:$AG$1 < EOMONTH(A2,11)),B2:AG2)*0.2)+SUMPRODUCT(--($B$1:$AG$1>=EOMONTH(A2,11)),B2:AG2)*0.1<?XML:NAMESPACE PREFIX = EOMONTH(A2,11)),B2 /><EOMONTH(A2,11)),B2:AG2)*0.2)+SUMPRODUCT(--($B$1:$AG$1><EOMONTH(A2,11)),B2:AG2)*0.2)+SUMPRODUCT(--($B$1:$AG$1>

Remove the spaces in the formula just before the second EOMONTH. I had to put them in so the formula would paste into this forum.

Mike
</EOMONTH(A2,11)),B2:AG2)*0.2)+SUMPRODUCT(--($B$1:$AG$1>
</EOMONTH(A2,11)),B2:AG2)*0.2)+SUMPRODUCT(--($B$1:$AG$1>