Dates and %'s

[Anx]

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

[Anx]

..or is there a way to extract dates from a column that are before a certain date please?

Thanks
Anx

[Anx]

Up

[Mark W.]

Your problem statement is rather vague. Provide some sample data and specify precisely the desired results.

[Anx]

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!!

[Anx]

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

[Mark W.]

Okay, how about...

*

Date	Price	Months	New Price
6/12/01	77.14	9	30.86
8/20/01	77.14	7	54.00
6/1/01	77.14	10	30.86
6/15/01	114.3	9	45.72
4/25/01	114.3	11	45.72
5/8/01	114.3	11	45.72
10/19/01	114.3	5	114.30
8/9/01	80.19	7	56.13
11/20/01	80.19	4	80.19
11/20/01	80.19	4	80.19

...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 ]

[Anx]

Brilliant, thanks very much

Anx