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

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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’)
 

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
644
Office Version
  1. 2016
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,480
Office Version
  1. 365
Platform
  1. Windows
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)
 

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
644
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,480
Office Version
  1. 365
Platform
  1. Windows
  • 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))
 

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
644
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
I'm wondering if some of the dates in column G have the wrong year in them
I suspect they are, so I changed the start date to 2021.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,778
Messages
5,626,827
Members
416,202
Latest member
donya ba

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
Top