# How do I calculate closest past & closest future dates base on multiple criteria

1. ## How do I calculate closest past & closest future dates base on multiple criteria

Hi,

This is a table made up of a list of Part Numbers (column A) and a corresponding delivery date (column B). In column C I have entered the following formula (after research online and in this forum):

=INDEX(\$B\$4:\$B\$778,MATCH(MIN(IF(\$A\$4:\$A\$778=\$A4,ABS(\$B\$4:\$B\$778-\$B\$1))),IF(\$A\$4:\$A\$778=\$A4,ABS(\$B\$4:\$B\$778-\$B\$1)),0))

I also use CTRL+SHIFT+ENTER when completing

I know my way around excel a little, but am certainly no guru. It is my belief that this formula is providing me with the closest delivery date prior to the date I have specified in cell B1. However for some reason a few dates seem to produce #VALUE! but I am unsure why?
I am not too fussed at this point about the results in column C where there is no delivery date supplied in column B

I have 2 main queries that I am seeking your help with:

(1) Is the formula in column C giving me the result I think it is (closest past date)? And if so, is it structured correctly or is it over complicated?

(2) How can this formula be amended to provide me with the closest future date (to be entered separately in column D for example)

Any help or feedback would be greatly appreciated!

Many thanks  Register To Reply

2. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Are you sure this formula works?

The first part number NP50232 has delivery of 21/04.
The same part number occurs twice more in the list with dates of 9/5 and 4/7
4/7 is the value returned by your formula in all cases, but 4/7 is NOT prior to 21/4 ???

What happens if there si no prior date, what result do you want then?  Register To Reply

3. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

I can see now that this formula is not actually doing what I wanted it to.

In all cases, the date I want to return last prior date and next future date is against cell B2 (16/06/2017). However the value being returned against Part Number NP50232 (value 04/07/2017) is clearly not prior to 16/06/2017!

If there is no prior or future date, just returning a blank cell would be fine in this particular case.  Register To Reply

4. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Try

For most recent past date
=IF(MAX(IF((A\$4:A\$778=A4)*(B\$4:B\$778<B4),B\$4:B\$778))=0,"",MAX(IF((A\$4:A\$778=A4)*(B\$4:B\$778<B4),B\$4:B\$778)))
Array formula, use Ctrl-Shift-Enter

For next future date
=IF(MIN(IF((A\$4:A\$778=A4)*(B\$4:B\$778>B4),B\$4:B\$778))=0,"",MAX(IF((A\$4:A\$778=A4)*(B\$4:B\$778>B4),B\$4:B\$778)))
Array formula, use Ctrl-Shift-Enter

and copy down the columns  Register To Reply

5. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

This is great, exactly what I was after!

One additional query - I have now added the Delivery Qty in column C (see attached updated file "Book1.2.xls") which I would like to link to each part number and respective recent/future delivery date. I thought one way of doing this could be to concatenate Part Number & Date and do some kind of lookup, however on occasion there are Part Numbers that have more than one delivery on the same date, plus a lookup would only pickup the first record it matches with.

Is there a way to pull the Qty across from column C and match it to the relevant most recent and next future dates, as calculated in your formula solution below?

I suspect maybe some kind of index/match is required.  Register To Reply

6. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Does this work? Check carefully the part numbers with more than one delivery on the same date, I think it's only going to pull the first one.

in E4
=IF(D4="","",VLOOKUP(A4,IF((B\$4:B\$778=D4),A\$4:C\$778),3,0))
Array formula, use Ctrl-Shift-Enter

in G4
=IF(F4="","",VLOOKUP(A4,IF((B\$4:B\$778=F4),A\$4:C\$778),3,0))
Array formula, use Ctrl-Shift-Enter  Register To Reply

7. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Hi, yes it only pulls the first value.
For example, Part Number RPUKSN10591 has 2 deliveries on 22/06 for two different quantities (17,430 & 6,608), however that formula picks up the 17,430 figure for both records.  Register To Reply

8. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

If you have a duplicate part number with multiple deliveries on the same date
how do you know which value you want to return, ie whats the rule to determine which value to return?  Register To Reply

9. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Good point! Thinking about it, I'd actually need to SUM the total delivery quantity on the same day for the same part number. So in the example of Part Number RPUKSN10591 I'd want the formula to show 24,038 (17,430 + 6,608).

It wouldn't matter too much if the 24,038 showed in both records (i.e. the 2 rows for the 2 deliveries on the same day) as if I reference this PNo / Del date / quantity in another sheet or a pivot for example, I could get round that by looking up the first value for each part number of by summarising by MAX or something in a pivot.  Register To Reply

10. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

Does this work?

in E4
=IF(D4="","",SUMPRODUCT((A\$4:A\$778=A4)*(B\$4:B\$778=D4)*(C\$4:C\$778)))

in G4
=IF(F4="","",SUMPRODUCT((A\$4:A\$778=A4)*(B\$4:B\$778=F4)*(C\$4:C\$778)))  Register To Reply

11. ## Re: How do I calculate closest past & closest future dates base on multiple criteria

I think it does yes! Can't find an example where it doesn't.

Special-K, thank you for your speedy help with this query today. This element is part of a much larger project and this issue with past/future dates relevant to unique part numbers had proved to be my stumbling block... until now!

Very much appreciate all your feedback and solutions.  Register To Reply