Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening disc
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening disc
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening
"Dave" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening
"Dave" wrote: 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!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add 30, 60 and 90 days to date and calculate deepening
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I calculate 180 days from any date? | Excel Worksheet Functions | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
How do I calculate 30 days later from a date | Excel Worksheet Functions | |||
Calculate # of Days from one date to another | Excel Worksheet Functions |