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
Glad we could help & thanks for the feedback.
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Fluff, how would i retrofit these two formulas to another excel worksheet with the columns in the same arrangement. just different worksheet name and tab names?
 
Upvote 0
Jimbo Jones said:
Hi Fluff, how would i retrofit these two formulas to another excel worksheet with the columns in the same arrangement. just different worksheet name and tab names?
Click to expand...
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
If the columns are the same, just change the sheet names to suit.
 
Upvote 0
That would suggest that col A is blank for the first matching value.
 
Upvote 0
Fluff said:
That would suggest that col A is blank for the first matching value.
Click to expand...
Usually when you copy paste a formula from one worksheet to another excel worksheet, excel understands and automatically references the new sheet and cells, when i copy paste it still references the old sheet?
 
Upvote 0
=INDEX(FILTER('[AIRT Data]Book1 (17)'!$B$2:$B$80,('[AIRT Data]Book1 (17)'!$C$2:$C$80=M4)*('[AIRT Data]Book1 (17)'!$B$2:$B$80>=N4)*('[AIRT Data]Book1 (17)'!$B$2:$B$80<=P4),"NA"),1)

=INDEX(FILTER('[MA AIRT Data]Book1 (17)'!$A$2:$A$80,('[MA AIRT Data]Book1 (17)'!$C$2:$C$80=M4)*('[MA AIRT Data]Book1 (17)'!$B$2:$B$80>=N4)*('[MA AIRT Data]Book1 (17)'!$B$2:$B$80<=P4),"NA"),1)
 
Upvote 0
Jimbo Jones said:
=INDEX(FILTER('[AIRT Data]Book1 (17)'!$B$2:$B$80,('[AIRT Data]Book1 (17)'!$C$2:$C$80=M4)*('[AIRT Data]Book1 (17)'!$B$2:$B$80>=N4)*('[AIRT Data]Book1 (17)'!$B$2:$B$80<=P4),"NA"),1)

=INDEX(FILTER('[MA AIRT Data]Book1 (17)'!$A$2:$A$80,('[MA AIRT Data]Book1 (17)'!$C$2:$C$80=M4)*('[MA AIRT Data]Book1 (17)'!$B$2:$B$80>=N4)*('[MA AIRT Data]Book1 (17)'!$B$2:$B$80<=P4),"NA"),1)
Click to expand...
This is what the formula looks like when i change the sheet reference in example one....the other is what it looks like when just copied into the worksheet no changes
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

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