How to add 30, 60 and 90 days to date and calculate deepening disc

[Hoosiermomma]

I am trying to set up a table on an inventory sheet that will show the date
item is received, then show date at date +30 days, date + 60 days and date +
90 days. I also need to show pricing discounts at 30, 60 and 90 days. Is
there a way to set this up so that these fields are automatically calculated
and entered when I enter the initial received date?

 

[Dave]

Hi,
If your initial received date is entered into, say, C10, then:
in C11: =C10+30
in C12: =C10+60
in C13: =C10+90
Be sure to format C11:C13 as date.
Need more detail on the second requirement.
Regards - Dave.

 

[Hoosiermomma]

Hi,
If your initial received date is entered into, say, C10, then:
in C11: =C10+30
in C12: =C10+60
in C13: =C10+90
Be sure to format C11:C13 as date.
Need more detail on the second requirement.
Regards - Dave

Thanks, Dave!
Do I have to do this on every row or can I enter the formula into the
columns of the table and have it fill in the rest of the row as the received
date is entered? As to the 2nd part: I want to list the initial price, then
the price -10% after 30 days, the price -20% after 60 days, and the price
-30% after 90 days.

 

[Dave]

Hi,
If your initial price is in, say, D10, and
If your initial received date is in, say, C10

then in another cell:

=IF(C10="","",IF(C10<(TODAY()-90),D10*0.7,IF(C10<(TODAY()-60),D10*0.8,IF(C10<(TODAY()-30),D10*0.9,D10))))

This will give you the price as you want, changing automatically as the
initial date becomes over 30, then 60, then 90 days old.
Is this what you need?

You can probably just fill across or down for other columns, but I would
have to know the layout of your sheet to be sure.

Regards - Dave.

 

[Hoosiermomma]

Hi,
If your initial price is in, say, D10, and
If your initial received date is in, say, C10

then in another cell:

=IF(C10="","",IF(C10<(TODAY()-90),D10*0.7,IF(C10<(TODAY()-60),D10*0.8,IF(C10<(TODAY()-30),D10*0.9,D10))))

This will give you the price as you want, changing automatically as the
initial date becomes over 30, then 60, then 90 days old.
Is this what you need?

You can probably just fill across or down for other columns, but I would
have to know the layout of your sheet to be sure.

Regards - Dave.

Thanks again!
Right now the sheet is set up as a table with columns labeled: Item#, Item,
Date In, Price, Date +30, Price -10%, Date + 60, Price - 20%, Date + 90,
Price-30%, Date Sold.
The rows will be used for listing the customer's merchandise. This is for a
consignment store. The sheet will be used for tracking each customer's item
as they are added to inventory. Thanks for your help!!!

 

[Dave]

Hi,
Sorry for the delay. Been a bit busy.
OK, I am assuming that your headers are in Row 1 and that your data starts
in Row 2. If this is not so, you will have to change all the 2's for whatever
row your data does start in. I have also assumed that 'Date In' is Column C
and 'Price' is Column D.
Remember to format Columns E, G, I as Date, before entering the formulas.

Row 2 of Date+30 Column: =IF(C2="","",C2+30)
Row 2 of Price-10% Column: =IF(D2="","",D2-(D2*0.1))
Row 2 of Date+60 Column: =IF(C2="","",C2+60)
Row 2 of Price-20% Column: =IF(D2="","",D2-(D2*0.2))
Row 2 of Date+90 Column: =IF(C2="","",C2+90)
Row 2 of Price-30% Column: =IF(D2="","",D2-(D2*0.3))

Ok, now select E2:J2 and fill down as far as you want.
Let me know if this is what you need.
Regards - Dave.