Percentage discount based on todays date....

**dawgpoundfan** · 07-09-2008, 12:11 PM

I have to apply a 3% discount to a bill rate for a consultant once he/she has been on site for 6 months and another 3% once they have been there for 12 months. Then I need to take out the pay rate for my margin. Right now I do all that but it is done based on a manually entered 1 or 2 stating if the discount has been applied. I would like to automate it based on the today's date.

I have the following cells on my spreadsheet:
C2 equals the start date (that is what I base my discount date on)

E2 equals the date the 3% should kick in (I would like to keep this since it gives me a heads up on what is coming)

F2 equals a 1(if the the 3% discount has been applied) or a 2(if it has not). I am a newbie at if/then and right now I have to manually update this once the discount date arrives.

G2 equals the date the 6% should kick in (same as above)

H2 equals a 1(if the 6% discount has been applied) or a 2 (if not). Again I manually have to change this to see the margin show correctly.

I2 equals the Pay Rate (this is what I subtract from the bill rate to give me my margin)

J2 equals the Bill Rate (this is what I apply the discount to)

K2 equals the margin (what my commissions are based on)

L2 equals the percent (should not impact this equation)

M2 equals what the Margin would be if the 3% was applied (I have it doing an if then statement based on a manual entry of whether or not the discount has kicked in...Se F2 and H2)

N2 equals what the margin would be if the 6% was applied (same as above).

Now I would like to M2 and N2 to still show because I like to see what it was previous. I would like a column that shows the original margin, the margin w/ 3% applied, and the margin with 6% applied...most important is one that shows the "current" or effective margin based on todays date

.

Clear as mud?

Sorry for the long winded question.

**Tnesper** · 07-09-2008, 01:45 PM

Its hard to tell without seeing the actual sheet, but try playing around with the month function.

you can do =month(cell link to date) - month(cell link to date) and that will give you the number of months between the dates. Then I would think you could do an if(or statement based on that value.

**dawgpoundfan** · 07-09-2008, 03:59 PM

First thank you very much for the response

I attached the form. What I need to have automated is that F2 and H2. If those auto fill based on whether or not the Start Date (C2) is 6 and 12 months old the rest I can work with. I feel like I am doing a roundabout way but as I said I am not an excel user past a simple if/then

.

BTW the CTO is our speak for margin.