Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the correct end date
Hi,
We have storage boxes that have a destroy date. The boxes hold 100 sales orders. Each sales order has a date closed. The box is scheduled to be destroyed 10 years after the close date of the 100th order number. This was not a good strategy because some of the orders closed after the 100th order in the box. So we may be destroying documents that should not be. I need help with a formula or vba to find out if an order's closed date is 10 years before the storage boxes destroy date I tried the following formula, but it doesn't work because I'm not determining the between order number part correctly. The details are described below my failed formula. =IF(AND(A23387,'0100 RETENTION (2)'!$I$119,'0100 RETENTION (2)'!$J $119,VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I$119:$K $262,3,FALSE)),IF(AND(A23387,'0100 RETENTION (2)'!$I$118,'0100 RETENTION (2)'!$J$118),VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I $119:$K$262,3,FALSE))) I appreciate your feedback. Thanks, Dan __________________________________________________ __ We have a storage log named "0100 RETENTION (2)" that shows the description and the order numbers in the box, like this: Begin End Order Order Number Number Destroy Date 60300 60399 Dec-2013 60900 60999 Jan-2017 61100 61144 May-2013 61200 61399 May-2013 61400 61499 Dec-2013 61500 61599 Jan-2014 61600 61699 Oct-2013 61700 61920 Dec-2013 62000 62199 Sep-2013 62300 62324 Jun-2014 We have a report from the system in another sheet named "closed" that looks like: Order # Status Close Date 62300 C 02/27/03 62325 C 02/28/03 62500 C 04/01/03 62600 C 03/07/03 62900 C 04/03/03 63000 C 03/28/03 63100 C 06/03/03 63200 C 04/07/03 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the correct end date
On 'Closed' sheet (assuming headers in Row 1) ---
in D2 enter =VLOOKUP(A2,'0100 RETENTION (2)'!A:C,3,TRUE) to get the 'Destroy' date of the order in A2 in E2 enter =DATE(YEAR(C2)+10,MONTH(C2),DAY(C2)) to add 10 years to the 'Close' date in F2 enter =IF(D2<E2,"Destroy date is less than close date+10 years","Ok to destroy") --------------------------------------------------------------------------------------------- You can also in G2 enter =VLOOKUP(A2,'0100 RETENTION (2)'!A:D,4,TRUE) to get the 'Lot#' after entering sequence number in Col D of '0100 RETENTION (2)' enter in E2 of '0100 RETENTION (2)' =SUMPRODUCT(--(Sheet2!$D$2:$D$11<Sheet2!E2),--(Sheet2!$F$2:$F$11='0100 RETENTION (2)'!D2)) and copy down to get the no of orders in each 'Lot' which should NOT be destroyed... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "dan dungan" wrote: Hi, We have storage boxes that have a destroy date. The boxes hold 100 sales orders. Each sales order has a date closed. The box is scheduled to be destroyed 10 years after the close date of the 100th order number. This was not a good strategy because some of the orders closed after the 100th order in the box. So we may be destroying documents that should not be. I need help with a formula or vba to find out if an order's closed date is 10 years before the storage boxes destroy date I tried the following formula, but it doesn't work because I'm not determining the between order number part correctly. The details are described below my failed formula. =IF(AND(A23387,'0100 RETENTION (2)'!$I$119,'0100 RETENTION (2)'!$J $119,VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I$119:$K $262,3,FALSE)),IF(AND(A23387,'0100 RETENTION (2)'!$I$118,'0100 RETENTION (2)'!$J$118),VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I $119:$K$262,3,FALSE))) I appreciate your feedback. Thanks, Dan __________________________________________________ __ We have a storage log named "0100 RETENTION (2)" that shows the description and the order numbers in the box, like this: Begin End Order Order Number Number Destroy Date 60300 60399 Dec-2013 60900 60999 Jan-2017 61100 61144 May-2013 61200 61399 May-2013 61400 61499 Dec-2013 61500 61599 Jan-2014 61600 61699 Oct-2013 61700 61920 Dec-2013 62000 62199 Sep-2013 62300 62324 Jun-2014 We have a report from the system in another sheet named "closed" that looks like: Order # Status Close Date 62300 C 02/27/03 62325 C 02/28/03 62500 C 04/01/03 62600 C 03/07/03 62900 C 04/03/03 63000 C 03/28/03 63100 C 06/03/03 63200 C 04/07/03 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the correct end date
Seems to me, after parsing out this formula, that you're missing the second
outcome (the value if FALSE) of both your IFs, the value if your first IF is false. I read it like this: =IF(AND(...),IF(AND(...),VLOOKUP)) ....where it should look like this: =IF(AND(...),IF(AND(...),VLOOKUP,OutcomeIfFalse),O utcomeIfFalse) And maybe I didn't read close enough, but I don't understand how this formula, even when corrected, is supposed to help you. You need a formula that can determine the proper destroy date, right? which is 10 years after the whichever order close date in that particular box is the latest. So what you want for each box is some formula that gives you MAX(CloseDateRange)+TenYears, no? --- "dan dungan" wrote: We have storage boxes that have a destroy date. The boxes hold 100 sales orders. Each sales order has a date closed. The box is scheduled to be destroyed 10 years after the close date of the 100th order number. This was not a good strategy because some of the orders closed after the 100th order in the box. So we may be destroying documents that should not be. I need help with a formula or vba to find out if an order's closed date is 10 years before the storage boxes destroy date I tried the following formula, but it doesn't work because I'm not determining the between order number part correctly. The details are described below my failed formula. =IF(AND(A23387,'0100 RETENTION (2)'!$I$119,'0100 RETENTION (2)'!$J $119,VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I$119:$K $262,3,FALSE)),IF(AND(A23387,'0100 RETENTION (2)'!$I$118,'0100 RETENTION (2)'!$J$118),VLOOKUP(VALUE(A23387),'0100 RETENTION (2)'!$I $119:$K$262,3,FALSE))) I appreciate your feedback. __________________________________________________ __ We have a storage log named "0100 RETENTION (2)" that shows the description and the order numbers in the box, like this: Begin End Order Order Number Number Destroy Date 60300 60399 Dec-2013 60900 60999 Jan-2017 61100 61144 May-2013 61200 61399 May-2013 61400 61499 Dec-2013 61500 61599 Jan-2014 61600 61699 Oct-2013 61700 61920 Dec-2013 62000 62199 Sep-2013 62300 62324 Jun-2014 We have a report from the system in another sheet named "closed" that looks like: Order # Status Close Date 62300 C 02/27/03 62325 C 02/28/03 62500 C 04/01/03 62600 C 03/07/03 62900 C 04/03/03 63000 C 03/28/03 63100 C 06/03/03 63200 C 04/07/03 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find the correct end date
Hi Bob,
You're right. I don't know how that formula was supposed to help me, either. But your idea of the vlookup with the array works great. Thanks, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you please hel me to find the correct formulas | Excel Worksheet Functions | |||
Trying to Find the Correct Answer-But not using Vlookup Function | Excel Worksheet Functions | |||
Trying to Find the Correct Answer-But not using Vlookup Function | Excel Worksheet Functions | |||
Trying to Find the Correct Answer-But not using Vlookup Function | Excel Worksheet Functions | |||
Solver does not find correct solution??? | Excel Discussion (Misc queries) |