Find Max date prior to specific date and min date after specific date for each ID

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
646
Office Version
  1. 2016
Platform
  1. Windows
I can't for the life of me figure this out. I've tried maxifs (don't have the function available in my version), vlookup, match, index etc. I just don't know where to start.

I have a list of ID's in one tab along with a start date and end date, Col A,B,C respectively. In a second tab I have an as of date, ID and a due date, again columns a,b,c respectively. There are duplicates in the second tab.

I need to do the following.

  • lookup the ID on the first tab
  • compare the start date to the as of date on the second tab
  • return the due date based on the most recent (max) as of date PRIOR to the start date.
  • compare the end date to the as of date on the second tab
  • return the due date based on the first (min) as of date AFTER the end date.
I've placed example data on one sheet for ease.
IDstart dateend dateMax_dateMin_dateas of dateIDdue date
123456789
5/30/2020​
9/26/2020​
4/30/2020​
12/1/2020​
4/15/2020​
123456789
1/1/2020​
987654321
10/2/2020​
1/26/2021​
9/27/2020​
6/2/2021​
4/18/2020​
123456789
4/30/2020​
9/25/2020​
123456789
9/1/2020​
9/26/2020​
123456789
10/1/2020​
9/27/2020​
123456789
12/1/2020​
9/28/2020​
123456789
12/12/2020​
10/1/2020​
123456789
12/21/2020​
9/29/2021​
987654321
9/22/2020​
9/30/2021​
987654321
9/23/2020​
10/1/2021​
987654321
9/27/2020​
10/2/2021​
987654321
9/28/2020​
1/24/2021​
987654321
10/8/2020​
1/25/2021​
987654321
10/9/2020​
1/26/2021​
987654321
5/2/2021​
1/27/2021​
987654321
6/2/2021​
1/28/2021​
987654321
7/1/2021​

I hope this makes sense. Thank you in advance.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2016 and done. Thank you.
 
Upvote 0
Either I'm not following correctly or your expected results are incorrect.

Note that the dates below are in UK format.
Book1
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202027/09/202015/04/202012345678901/01/2020
398765432102/10/202026/01/202128/09/202027/01/202118/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
Sheet4
Cell Formulas
RangeFormula
D2:D3D2=AGGREGATE(14,6,$I$2:$I$17/($I$2:$I$17<B2)/($H$2:$H$17=A2),1)
E2:E3E2=AGGREGATE(15,6,$G$2:$G$17/($G$2:$G$17>C2)/($H$2:$H$17=A2),1)
 
Upvote 0
Either I'm not following correctly or your expected results are incorrect.

Note that the dates below are in UK format.
Book1
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202027/09/202015/04/202012345678901/01/2020
398765432102/10/202026/01/202128/09/202027/01/202118/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
Sheet4
Cell Formulas
RangeFormula
D2:D3D2=AGGREGATE(14,6,$I$2:$I$17/($I$2:$I$17<B2)/($H$2:$H$17=A2),1)
E2:E3E2=AGGREGATE(15,6,$G$2:$G$17/($G$2:$G$17>C2)/($H$2:$H$17=A2),1)
Thank you for the quick reply. I think we are close. The returned values for D and E should always be a due date. It looks as though some are returning the as of date.
 
Upvote 0
I'm also getting a different answer
+Fluff 1.xlsm
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202001/10/202015/04/202012345678901/01/2020
398765432102/10/202126/01/202101/07/202122/09/202018/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
18
Test
Cell Formulas
RangeFormula
D2:D3D2=AGGREGATE(14,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17<=B2),1)
E2:E3E2=AGGREGATE(15,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17>=C2),1)
 
Upvote 0
Just realised it should be
+Fluff 1.xlsm
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202001/12/202015/04/202012345678901/01/2020
398765432102/10/202126/01/202101/07/202122/09/202018/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
Test
Cell Formulas
RangeFormula
D2:D3D2=AGGREGATE(14,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17<=B2),1)
E2:E3E2=AGGREGATE(15,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17>C2),1)
 
Upvote 0
Solution
  • lookup the ID on the first tab
  • compare the start date to the as of date on the second tab
  • return the due date based on the most recent (max) as of date PRIOR to the start date.
I'm still not following 100%, there is no 'as of date' prior to the 'start date' for the second ID, so how are you getting your expected result?

I thought this might work, but when I was double checking my results I noticed that the dates in the main table don't appear to be in order, if that is the case then this will fail.

edit:- @Fluff, your revised suggestion is one that I aborted earlier, it doesn't work for the second ID, although I'm wondering if some of the dates in column G have the wrong year in them :unsure:

Book1
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202001/12/202015/04/202012345678901/01/2020
398765432102/10/202026/01/2021#N/A22/09/202018/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
Sheet4
Cell Formulas
RangeFormula
D2:D3D2=INDEX($I$2:$I$17,MATCH(2,1/($H$2:$H$17=A2)/($G$2:$G$17<=B2)))
E2:E3E2=INDEX($I$2:$I$17,MATCH(1,1/($H$2:$H$17=A2)/($G$2:$G$17>C2),0))
 
Upvote 0
Just realised it should be
+Fluff 1.xlsm
ABCDEFGHI
1IDstart dateend dateMax_dateMin_dateas of dateIDdue date
212345678930/05/202026/09/202030/04/202001/12/202015/04/202012345678901/01/2020
398765432102/10/202126/01/202101/07/202122/09/202018/04/202012345678930/04/2020
425/09/202012345678901/09/2020
526/09/202012345678901/10/2020
627/09/202012345678901/12/2020
728/09/202012345678912/12/2020
801/10/202012345678921/12/2020
929/09/202198765432122/09/2020
1030/09/202198765432123/09/2020
1101/10/202198765432127/09/2020
1202/10/202198765432128/09/2020
1324/01/202198765432108/10/2020
1425/01/202198765432109/10/2020
1526/01/202198765432102/05/2021
1627/01/202198765432102/06/2021
1728/01/202198765432101/07/2021
Test
Cell Formulas
RangeFormula
D2:D3D2=AGGREGATE(14,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17<=B2),1)
E2:E3E2=AGGREGATE(15,6,$I$2:$I$17/($H$2:$H$17=A2)/($G$2:$G$17>C2),1)
I think this gets me to where I need to be. Thank you! I will let you know if I have any quesitons.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top