How to find the correct end date

[dan dungan]

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

 

[Sheeloo]

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

 

[Bob Bridges]

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),OutcomeIfFalse)

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]

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