Dates and %'s

Anx

New Member
Joined
Mar 31, 2002
Messages
22
I have 2 sheets with various dates and prices on them with regards to stock, what i need to do is link them to a third sheet with this data being the outcome;

Products more than 6 months old - 30% off price
Products more than 9 months old - 60% off price

I also need to make the % variable by the user and am not allowed to use macros.

Could anyone help me with this please ?

Thanks very much

Anx
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
..or is there a way to extract dates from a column that are before a certain date please?

Thanks
Anx
 
Upvote 0
Your problem statement is rather vague. Provide some sample data and specify precisely the desired results.
 
Upvote 0
Ok, I have a list of dates (and prices relating to them) from which i need to calculate which ones are more than 6 months old and which are more than 9 months old from a variable date to be entered by the user.

when this has been worked out i should now have 2 lists of dates.

From these dates I need to reduce the prices by 30% for dates more than 6 months old and 60% for dates more than 9 months old.

I am also not allowed to use a macro.

Help!! :)
 
Upvote 0
12/06/2001 77.14
20/08/2001 77.14
01/06/2001 77.14
15/06/2001 114.30
25/04/2001 114.30
08/05/2001 114.30
19/10/2001 114.30
09/08/2001 80.19
20/11/2001 80.19

etc etc
 
Upvote 0
Okay, how about...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Date</td><td>Price</td><td>Months</td><td>New Price</td></tr><tr><td>6/12/01</td><td>77.14</td><td>9</td><td>30.86</td></tr><tr><td>8/20/01</td><td>77.14</td><td>7</td><td>54.00</td></tr><tr><td>6/1/01</td><td>77.14</td><td>10</td><td>30.86</td></tr><tr><td>6/15/01</td><td>114.3</td><td>9</td><td>45.72</td></tr><tr><td>4/25/01</td><td>114.3</td><td>11</td><td>45.72</td></tr><tr><td>5/8/01</td><td>114.3</td><td>11</td><td>45.72</td></tr><tr><td>10/19/01</td><td>114.3</td><td>5</td><td>114.30</td></tr><tr><td>8/9/01</td><td>80.19</td><td>7</td><td>56.13</td></tr><tr><td>11/20/01</td><td>80.19</td><td>4</td><td>80.19</td></tr><tr><td>11/20/01</td><td>80.19</td><td>4</td><td>80.19</td></tr></table></body></html>
...where...

'Months' uses =DATEDIF('Date',TODAY(),"M")
'New Price' uses ='Price'*CHOOSE(SUM(('Months'>={0,6,9})+0),1,0.7,0.4)

Note: TODAY() can be replaced by a reference to a cell containing a valid data value.
This message was edited by Mark W. on 2002-04-08 10:24
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top