RETURN a DATE from one worksheet in between a DATE range with serial number as unique identifier

J

Jimbo Jones

New Member
Joined
May 12, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I need a formula to return the Maintenance Date(Column B)(Maintenance Date Worksheet) to the Actual Ship & Return Dates Worksheet in Column C. Basically the Asset has a serial number; it is shipped to the worksite then returned to the base, maintenance is performed, then the serialized asset is returned to the worksite. The transactions are done in two different computer systems which is why i need to join the date ranges to make sure Maintenance is being done between the actual ship date and the return date. The unique identifier is the serial number column on both worksheets.

MR EXCEL WORKBOOK.xlsx
ABCD
1Lot Serial NumberActual Ship DateMaintenance DateReturn Date
56CEM01011/24/202011/29/2020
57CEM01011/24/202011/26/2020
58CEM01011/24/202011/26/2020
59CEM01110/3/202010/5/2020
60CEM01110/9/202010/20/2020
61CEM01110/18/202010/21/2020
62CEM01110/19/202010/23/2020
63CEM01110/24/202010/25/2020
64CEM01110/25/202011/1/2020
65CEM01110/25/202010/30/2020
66CEM01110/25/202010/30/2020
67CEM01110/28/202011/3/2020
68CEM01111/1/202011/2/2020
69CEM01111/2/202011/9/2020
70CEM01111/5/202011/9/2020
71CEM01111/5/202011/7/2020
72CEM01111/10/202011/12/2020
73CEM01410/18/202010/22/2020
74CEM01410/28/202011/8/2020
75CEM01411/12/202011/20/2020
76CEM01411/24/202012/3/2020
77CEM01412/4/202012/6/2020
78CEM01412/11/202012/15/2020
79CEM01412/15/202012/24/2020
80CEM0141/2/20211/5/2021
81CEM0142/15/20212/21/2021
82CEM0143/14/20213/14/2021
83CEM0143/19/20213/22/2021
84CEM0144/5/20214/15/2021
85CEM01811/12/202011/14/2020
86CEM01811/15/202011/16/2020
87CEM01811/17/202011/18/2020
88CEM0181/3/20211/9/2021
89CEM0181/4/20211/7/2021
90CEM0181/6/20211/9/2021
91CEM0181/6/20211/9/2021
92CEM0181/20/20211/31/2021
93CEM0181/31/20211/31/2021
94CEM0182/1/20212/19/2021
95CEM0182/1/20212/8/2021
96CEM0182/3/20212/11/2021
97CEM0182/7/20212/11/2021
98CEM0182/14/20212/17/2021
99CEM0182/14/20212/16/2021
100CEM0182/14/20212/15/2021
101CEM0182/21/20212/28/2021
102CEM0182/21/20212/24/2021
103CEM0182/21/20212/22/2021
104CEM0183/3/20213/6/2021
105CEM0183/7/20213/11/2021
106CEM0183/7/20213/10/2021
107CEM0183/7/20213/10/2021
108CEM0183/7/20213/7/2021
109CEM0183/7/20213/7/2021
Actual Ship & Return Dates



MR EXCEL WORKBOOK.xlsx
AB
1Serial NumMaintenance Date
11CEM00111/27/2020
12CEM00111/28/2020
13CEM00111/30/2020
14CEM00611/24/2020
15CEM00611/25/2020
16CEM00711/24/2020
17CEM00711/27/2020
18CEM00711/30/2020
19CEM0107/18/2020
20CEM0109/8/2020
21CEM01010/2/2020
22CEM01010/20/2020
23CEM01011/20/2020
24CEM01011/27/2020
25CEM0119/23/2020
26CEM01111/22/2020
27CEM0114/20/2021
28CEM0148/4/2020
29CEM0149/25/2020
30CEM0149/27/2020
31CEM0149/28/2020
32CEM0149/28/2020
33CEM0149/28/2020
34CEM0149/29/2020
35CEM01410/6/2020
36CEM01411/1/2020
37CEM01411/10/2020
38CEM01411/11/2020
39CEM01411/22/2020
40CEM01411/24/2020
41CEM01412/9/2020
42CEM01412/11/2020
43CEM0141/5/2021
44CEM0141/23/2021
45CEM0141/30/2021
46CEM0142/14/2021
47CEM0142/14/2021
48CEM0142/19/2021
49CEM0142/21/2021
50CEM0186/26/2020
51CEM0186/29/2020
52CEM0189/6/2020
53CEM0189/6/2020
54CEM0189/16/2020
55CEM0189/24/2020
56CEM0189/26/2020
57CEM01810/20/2020
58CEM01811/20/2020
59CEM01812/6/2020
60CEM01812/8/2020
61CEM01812/26/2020
62CEM01812/26/2020
63CEM01812/26/2020
64CEM01812/26/2020
65CEM0181/4/2021
66CEM0181/13/2021
67CEM0181/22/2021
68CEM0181/31/2021
69CEM0182/5/2021
70CEM0182/11/2021
71CEM0182/13/2021
72CEM0182/24/2021
73CEM0183/7/2021
74CEM0183/13/2021
75CEM0183/31/2021
76CEM0184/7/2021
77CEM0184/24/2021
78CEM0184/25/2021
79CEM0184/25/2021
80CEM0184/26/2021
MAINTENANCE DATE
 
Sort by date Sort by votes
Fluff said:
How about
+Fluff 1.xlsm
ABCD
1Lot Serial NumberActual Ship DateMaintenance DateReturn Date
2CEM01024/11/202027/11/202029/11/2020
3CEM01024/11/2020NA26/11/2020
4CEM01024/11/2020NA26/11/2020
5CEM01103/10/2020NA05/10/2020
6CEM01109/10/2020NA20/10/2020
7CEM01118/10/2020NA21/10/2020
8CEM01119/10/2020NA23/10/2020
9CEM01124/10/2020NA25/10/2020
10CEM01125/10/2020NA01/11/2020
11CEM01125/10/2020NA30/10/2020
12CEM01125/10/2020NA30/10/2020
13CEM01128/10/2020NA03/11/2020
14CEM01101/11/2020NA02/11/2020
15CEM01102/11/2020NA09/11/2020
16CEM01105/11/2020NA09/11/2020
17CEM01105/11/2020NA07/11/2020
18CEM01110/11/2020NA12/11/2020
19CEM01418/10/2020NA22/10/2020
20CEM01428/10/202001/11/202008/11/2020
21CEM01412/11/2020NA20/11/2020
22CEM01424/11/202024/11/202003/12/2020
23CEM01404/12/2020NA06/12/2020
24CEM01411/12/202011/12/202015/12/2020
25CEM01415/12/2020NA24/12/2020
26CEM01402/01/2021NA05/01/2021
27CEM01415/02/202119/02/202121/02/2021
28CEM01414/03/2021NA14/03/2021
29CEM01419/03/2021NA22/03/2021
30CEM01405/04/2021NA15/04/2021
31CEM01812/11/2020NA14/11/2020
32CEM01815/11/2020NA16/11/2020
33CEM01817/11/2020NA18/11/2020
34CEM01803/01/202104/01/202109/01/2021
35CEM01804/01/202104/01/202107/01/2021
36CEM01806/01/2021NA09/01/2021
37CEM01806/01/2021NA09/01/2021
38CEM01820/01/202122/01/202131/01/2021
39CEM01831/01/2021NA31/01/2021
40CEM01801/02/202105/02/202119/02/2021
41CEM01801/02/202105/02/202108/02/2021
42CEM01803/02/202105/02/202111/02/2021
43CEM01807/02/2021NA11/02/2021
44CEM01814/02/2021NA17/02/2021
45CEM01814/02/2021NA16/02/2021
46CEM01814/02/2021NA15/02/2021
47CEM01821/02/202124/02/202128/02/2021
48CEM01821/02/2021NA24/02/2021
49CEM01821/02/2021NA22/02/2021
50CEM01803/03/2021NA06/03/2021
51CEM01807/03/202107/03/202111/03/2021
52CEM01807/03/202107/03/202110/03/2021
53CEM01807/03/202107/03/202110/03/2021
54CEM01807/03/2021NA07/03/2021
55CEM01807/03/2021NA07/03/2021
Sheet3
Cell Formulas
RangeFormula
C2:C55C2=INDEX(FILTER('Maintenance Date'!$B$2:$B$80,('Maintenance Date'!$A$2:$A$80=A2)*('Maintenance Date'!$B$2:$B$80>=B2)*('Maintenance Date'!$B$2:$B$80<=D2),"NA"),1)
Click to expand...
This is exactly what i am looking for. now can i throw in a caveat? with the actual data so i can get the exact formula i need?
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Do you mean you have simplified your requirement & this is not what you actually wanted?
 
Upvote 0
FLUFF's formula is, of course, the simplest approach to solving your problem. What change(s) are you making to the spec you initially stated?
 
Upvote 0
Hi Fluff, no I mean that the formula that kweaver provided was for the pseudo data i added in the beginning of the conversation. I would like to add the real data through xl2bb mini sheet so that the formula is applicable to my real sheet i am working on. I apologize i should have done that in the first place.
 
Upvote 0
You can post your real data, not a problem.
 
Upvote 0
kweaver said:
FLUFF's formula is, of course, the simplest approach to solving your problem. What change(s) are you making to the spec you initially stated?
Click to expand...
ADOPTION PERCENTAGE - HISTORY JDE DT DATA and MA AIRT DATA as of 4_30_2021.xlsm
MNOP
1Serial NumberActual Ship DateAIRT DATEReturn Date
2SPU11910/9/202011/30/2020
3SPU11911/30/202012/14/2020
4SPU11912/14/20201/23/2021
5SPU11912/14/202012/14/2020
6SPU1191/23/20213/8/2021
7SPU09010/9/202011/30/2020
8SPU09011/30/202012/14/2020
9SPU09012/14/20201/23/2021
10SPU09012/14/202012/14/2020
11SPU0901/23/20213/8/2021
12SPU08711/16/202011/23/2020
13SPU08711/23/202011/25/2020
14SPU0871/18/20211/31/2021
15SPU0871/31/20212/8/2021
16SPU0872/8/20212/17/2021
17SPU0872/17/20212/19/2021
18SPU0872/19/20213/1/2021
19SPU0873/1/20213/5/2021
20SPU0873/1/20213/1/2021
21SPU0873/5/20213/9/2021
22SPU0873/9/20213/22/2021
23SPU0873/22/20214/5/2021
24SPU08611/16/202011/23/2020
25SPU08611/23/202011/25/2020
JDE DT DATA 1


ADOPTION PERCENTAGE - HISTORY JDE DT DATA and MA AIRT DATA as of 4_30_2021.xlsm
ABC
1AIRT NumAIRT DATESerial Num
212536220154-ART10/21/2020SPU119
323285620105-ART11/23/2020SPU119
412536220114-ART8/14/2020SPU090
512536220117-ART9/14/2020SPU090
612536221179-ART3/25/2021SPU090
712536220102-ART7/14/2020SPU089
812536220103-ART7/20/2020SPU089
912536220104-ART7/20/2020SPU089
1012536220105-ART7/27/2020SPU088
1112536221186-ART4/28/2021SPU078
1223285620108-ART11/23/2020SPU078
1312536220147-ART10/19/2020SPU069
1412536221191-ART4/28/2021SPU069
1523285620114-ART11/23/2020SPU069
1623285620118-ART12/12/2020SPU069
1712536220101-ART7/14/2020SPU067
1812536220107-ART7/28/2020SPU066
1912536220108-ART7/28/2020SPU065
2012536220100-ART7/10/2020SPU064
MA AIRT DATA


In addition to changing the column references, tab or worksheet name references, and changing Maintenance Date to AIRT Date. I would like to bring in the AIRT number with the AIRT date?? Gosh i feel like i should be buying you folks a dinner and a movie after all the help here.
 
Upvote 0
Which column should the AIRT Num goto?
 
Upvote 0
Ok how about
Excel Formula: 
=INDEX(FILTER('MA AIRT Data'!$B$2:$B$80,('MA AIRT Data'!$C$2:$C$80=M2)*('MA AIRT Data'!$B$2:$B$80>=N2)*('MA AIRT Data'!$B$2:$B$80<=P2),"NA"),1)
and
Excel Formula: 
=INDEX(FILTER('MA AIRT Data'!$A$2:$A$80,('MA AIRT Data'!$C$2:$C$80=M2)*('MA AIRT Data'!$B$2:$B$80>=N2)*('MA AIRT Data'!$B$2:$B$80<=P2),"NA"),1)
 
Upvote 0
Solution
Fluff said:
Ok how about
Excel Formula: 
=INDEX(FILTER('MA AIRT Data'!$B$2:$B$80,('MA AIRT Data'!$C$2:$C$80=M2)*('MA AIRT Data'!$B$2:$B$80>=N2)*('MA AIRT Data'!$B$2:$B$80<=P2),"NA"),1)
and
Excel Formula: 
=INDEX(FILTER('MA AIRT Data'!$A$2:$A$80,('MA AIRT Data'!$C$2:$C$80=M2)*('MA AIRT Data'!$B$2:$B$80>=N2)*('MA AIRT Data'!$B$2:$B$80<=P2),"NA"),1)
Click to expand...
Absolutely Brilliant!!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
176
kerm007
K
Cubist
  • Solved
Return values based on date range
Replies
3
Views
168
Peter_SSs
Peter_SSs
F
  • Solved
Sort scripture references by chapter number
Replies
9
Views
232
Peter_SSs
Peter_SSs
S
  • Solved
lookup return from range of dates
Replies
4
Views
142
Peter_SSs
Peter_SSs
SanjayGMusafir
  • Solved
Filtering Dates within the dates and adding corresponding values
Replies
4
Views
54
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,361
Messages
6,124,500
Members
449,166
Latest member
hokjock

Share this page

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