Parameters in Excel

B

Brandon Williams

I am trying to figure out if there is a way to set sliding scale in Excel. I
am trying to create a commission calculation sheet and the agents get paid a
bonus if they reach a certain pay grade within six months. I need for Excel
to look at the dates and determine if the date that is entered in is more
than six months after the dates that are entered on the other sheets. Each
workbook will have around 50 to 100 worksheets and I need it to look at all
the dates. I realize that I may have to create another sheet that has all the
dates and amounts on it but what function should I use to get Excel to
determine if the dates are within the six month parameter and return the
dollar amount that is adjacent to it? If anyone has any suggestions they will
be greatly appreciated. Thanks in advance.
 
G

Gary''s Student

Hi Brandon

In A1, I put an old date: 11/8/2004
In B1, I put today: 11/17/2005

In C1: =IF(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) < B1,1,0)

This will return 1 if today is more than 6 months past the date in A1,
otherwise 0.

Perhaps you could adapt this formula to your application.
 
D

Dave Peterson

Kind of...

But excel is smart enough to do a little date arithmetic of its own. It'll
notice that the month is 17, add one to the year and make the month 5.

It's pretty neat with days, too:

try:
=date(2005,0,55)
or
=date(2005,0,0)

to see how it works.
 
Top