Percentage discount based on todays date....
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.
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.
Here is what it looks like
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.
Last edited by dawgpoundfan; 07-09-2008 at 04:23 PM.
If I've understood the attached sheet
Place in cell F2
Then in cell H2
Hope than helps
Have I made you happy ???
If yes, please make me happy by pressing the Add Reputation
button in my post.
Please don't forget to do the same to other contributors of this forum.
I don't void confusion, I create it
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1