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
The the workbook & sheet names correct?
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
In that case you will need to change the names.
 
Upvote 0
Fluff said:
In what way?
Click to expand...
I figured it out, the cell references were advanced over 1, so if the proper cell reference was N2 when i copied the formula in it changed to O2..... etc...

Quick question.?

Can the cell ranges - in the formulas you provided.... Like B2:B80. can i substitute the range for the entire column? So instead of the B2:B80 can i just do B
 
Upvote 0
You can use entire column references, but it might bring your workbook to a crawl.
 
Upvote 0
Hi Fluff,
For some reason I am still getting (0)Zero as a result in some cells? It seems random as i cannot find a reason for this result? I have no blanks at all in my data set......
 
Upvote 0
Without being able to see your actual data, there's not much I can do.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,514
Messages
6,125,267
Members
449,219
Latest member
daynle

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