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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Please use XL2BB to display sample data and also show a few expected/desired results. Thanks.
 
Upvote 0
Sorry, so you did! Would you indicate a few expected values?
 
Upvote 0
kweaver said:
Please use XL2BB to display sample data and also show a few expected/desired results. Thanks.
Click to expand...

kweaver said:
Sorry, so you did! Would you indicate a few expected values?
Click to expand...
So the Pink color is the expected results due to the Maintenance date falling in between the Actual ship and return date.

MR EXCEL WORKBOOK.xlsx
ABCD
1Lot Serial NumberActual Ship DateMaintenance DateReturn Date
56CEM01011/24/202011/27/202011/29/2020
57CEM01011/24/202011/27/202011/26/2020
58CEM01011/24/202011/27/202011/26/2020
Actual Ship & Return Dates


MR EXCEL WORKBOOK.xlsx
AB
1Serial NumMaintenance Date
19CEM0107/18/2020
20CEM0109/8/2020
21CEM01010/2/2020
22CEM01010/20/2020
23CEM01011/20/2020
24CEM01011/27/2020
MAINTENANCE DATE
 
Upvote 0
But, for CEM010, the main date of 11/27/2020 does NOT fall between the 24th and 26th.
 
Upvote 0
Well, try this after addressing what happens when the maint date doesn't fall between?

Code: 
Sub MaintDate()
Dim lra As Long, lrm As Long, i As Long, j As Long, ctm As Long, ftm As Long
lra = Cells(Rows.Count, "A").End(xlUp).Row ' Actual
lrm = Sheets("MAINTENANCE DATE").Cells(Rows.Count, "A").End(xlUp).Row ' Maintenance

For i = 56 To lra
ctm = WorksheetFunction.CountIf(Sheets("MAINTENANCE DATE").Range("A19:A" & lrm), Cells(i, 1))
ftm = 18 + WorksheetFunction.Match(Cells(i, 1), Sheets("MAINTENANCE DATE").Range("A19:A" & lrm), 0)
 For j = ftm To ftm + ctm - 1
  If Sheets("MAINTENANCE DATE").Cells(j, 2) >= Cells(i, 2) And Sheets("MAINTENANCE DATE").Cells(j, 2) <= Cells(i, 4) Then
   Cells(i, 3) = Sheets("MAINTENANCE DATE").Cells(j, 2).Value
   Else
  End If
  Next j
Next i
End Sub
 
Upvote 0
kweaver said:
Well, try this after addressing what happens when the maint date doesn't fall between?

Code: 
Sub MaintDate()
Dim lra As Long, lrm As Long, i As Long, j As Long, ctm As Long, ftm As Long
lra = Cells(Rows.Count, "A").End(xlUp).Row ' Actual
lrm = Sheets("MAINTENANCE DATE").Cells(Rows.Count, "A").End(xlUp).Row ' Maintenance

For i = 56 To lra
ctm = WorksheetFunction.CountIf(Sheets("MAINTENANCE DATE").Range("A19:A" & lrm), Cells(i, 1))
ftm = 18 + WorksheetFunction.Match(Cells(i, 1), Sheets("MAINTENANCE DATE").Range("A19:A" & lrm), 0)
 For j = ftm To ftm + ctm - 1
  If Sheets("MAINTENANCE DATE").Cells(j, 2) >= Cells(i, 2) And Sheets("MAINTENANCE DATE").Cells(j, 2) <= Cells(i, 4) Then
   Cells(i, 3) = Sheets("MAINTENANCE DATE").Cells(j, 2).Value
   Else
  End If
  Next j
Next i
End Sub
Click to expand...
Hi Kweaver, thank you..... now please help with how to implement, as i am very much a novice when it comes to VBA code. I am getting syntax error......
 
Upvote 0
Jimbo Jones said:
Hi Kweaver, thank you..... now please help with how to implement, as i am very much a novice when it comes to VBA code. I am getting syntax error......
Click to expand...
oh and if the maint date doesnt chronologically fit, then just return #N/A
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
153
kerm007
K
C
  • Solved
Return values based on date range
Replies
3
Views
152
Peter_SSs
Peter_SSs
F
  • Solved
Sort scripture references by chapter number
Replies
9
Views
216
Peter_SSs
Peter_SSs
S
  • Solved
lookup return from range of dates
Replies
4
Views
88
Peter_SSs
Peter_SSs
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
359
kevin9999
K

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

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