Here's another approach that you might like to play about with. Set up
a little table somewhere as follows:

0 0%
1 2.5%
2 5.0%
3 8.0%
4 11%
5 14%

You can give this table a name, and as you can see it represents the
cumulative percentage increase from the starting salary. I have
assumed that in year 2 the increase is 5% on the starting salary,
rather than 2.5% on top of 102.5 from the previous year, but if this
is the case then you can easily re-work the figures. Then basically
you will want the working year from the start date up to present -
something like this will give you that:

=INT((TODAY()-C$2)/365.25)

but instead of TODAY() you might like to use the DATE formula you
already have.

Then you can have a formula like:

=IF(calc_year6,(100+calc_year+14)/100,1+
VLOOKUP(calc_year,table_name,2))*F2/12

where calc_year is the above formula. This will give you the monthly
salary increased by the appropriate percentage.

Hope this helps.

Pete


On Apr 24, 12:50 am, spence wrote:
This is exactly the kind of syntax I was looking for, so thanks very much for
your excellent guidance. Your help was exactly the jumping-off point I needed
to puzzle this thing out. However, I'm running into a couple of challenges
that I hope you might be able to assist me with. I'm pasting my adaptation of
your formula below for you to see. The example from my original post was
simplified for clarity's sake, but I'm going to give you the more detailed
scenario here so we're on the same page and so you'll understand the
alternations I've made to your sampe formula:

Actual wage schedule:
1. 2.5% at one year anniversary
2. 2.5% at two year anniversary
3. 3% at years three, four, and five anniversaries
4. 1% every year thereafter

My anniversary dates are in column C and the starting wage is in column F.
My row of months begins in row G. So:

=IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1826),( F4*1.03),IF(G$1=DATE(YEAR*($C4),MONTH($C4),DAY($C4 )+1461),
(F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4 )+1096), (F4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+731), (F4*1.025),
IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+365), (F4*1.025),F4)))))

If it's not obvious, the values for a given month are based on the pay from
the previous month.

I was having two problems with your formula:

1. Using the Year count caused my wage increases to fall one month too late
(e.g. a start date of 11/01/06 should have produced a 2.5% raise as of
11/01/07. but instead didn't show the increase until 12/01/07.) I solved this
by changing to a Day count which is working but which seems like a terrible
idea...the leap years had me baffled for a good couple of hours. Perhaps
there's a better way to do this and get the same result?

2. Using "" caused a problem because once an anniversary date was reached,
every month following showed the same % increase since those months also
matched the criteria in the IF statement. I resolved this by using "="
instead of "". This works fine except for those years AFTER the fifth year
when I need to continue to show an annual increase of 1% for perpetuity. The
only solution I can see is to add IF statements to the forumla for year 5, 6,
7, 8...etc. This doesn't seem like a good way to solve the problem.

Any thoughts?

Thanks again for your prior assistance. I've been at this all day but it's
been worth the learning experience and then some.



"William Horton" wrote:
Would you be able to use something like the following...

=IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MO*NTH($B2),DAY($B2)),3%,IF (C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%)) )

That will show whether 3%, 2%, 1%, or 0% needs to be applied.

Hope that is a start.

"spence" wrote:

I have a five year month-by-month personnel budget with the staff names down
column A and their start dates in columb B. The months and years through 2012
go across row 1.

My question is, is there a way to build formulas that will calculate annual
wage increases based on start date where the increase is:

1. 2% at year one anniversary
2. 3% at year two anniversary
3. 1% at every anniversary thereafter

Thanks.- Hide quoted text -

- Show quoted text -