formula to match a date in one column with a date in another

M

mojo707

New Member
Joined
Jun 7, 2015
Messages
21
I'm trying to find a way (I've looked at Index, match, choose, find, search, substitute and am investigating solver) to match from a list of possible project end dates (demobilization dates) the date closest to each employees contract expiry date. The difficulty is that there will be a finite number of demobilization dates and once one is committed (i.e. matched to an employees contract expiry date, it will be unavailable for further matching). The sample worksheet below shows the desired output from the formula that I am seeking for Column E. In the sample you will see that for the Welder, the Desired Demob Date remains unchanged since a Welder cannot be swapped out for an Electrician whereas the Desired Demob Date for Electricians can change and an Electrician can be moved from one team to another.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Employee ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]Worker
Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl67, width: 130"]Contract
Expiry Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl67, width: 127"]Current
Demob Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl67, width: 129"]Desired
Demob Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl64, width: 130"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]08/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Electrician[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]06/30/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]07/31/19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Welder[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl66, width: 130"]05/31/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]06/30/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD="class: xl66, width: 129"]06/30/20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Maybe...

E2 copied down
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Hope this helps

M.
 
Upvote 0
Thanks M for responding, however I couldn't get your formula to work. I think perhaps because I may not have been clear enough in my description. I can't find a way to edit my post now so I will try to explain... Column E is not data but rather it is what my expectation of the solution would provide. I will try to upload the sample worksheet for you
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee ID[/TD]
[TD]Worker Type[/TD]
[TD]Contract Expiry Date[/TD]
[TD]Current Demob Date[/TD]
[TD]Desired Demob Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]Electrician[/TD]
[TD]08/31/19[/TD]
[TD]05/31/20[/TD]
[TD]08/31/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1235[/TD]
[TD]Electrician[/TD]
[TD]07/31/19[/TD]
[TD]08/31/19[/TD]
[TD]07/31/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1236[/TD]
[TD]Electrician[/TD]
[TD]06/30/19[/TD]
[TD]07/31/19[/TD]
[TD]05/31/20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1237[/TD]
[TD]Welder[/TD]
[TD]05/31/20[/TD]
[TD]06/30/20[/TD]
[TD]06/30/20[/TD]
[/TR]
</tbody>[/TABLE]

Note that column E is not data but to explain what the expected output of the solution would provide
 
Last edited:
Upvote 0
mojo707 said:
Note that column E is not data but to explain what the expected output of the solution would provide
Click to expand...

Yes, I fully understood that column E only shows the desired results.
Have you tested the formula? It worked perfectly for me


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Employee ID​
[/TD]
[TD]
Worker Type​
[/TD]
[TD]
Contract Expiry Date​
[/TD]
[TD]
Current Demob Date​
[/TD]
[TD]
Desired Demob Date​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
08/31/2019​
[/TD]
[TD]
05/31/2020​
[/TD]
[TD]
08/31/2019​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
07/31/2019​
[/TD]
[TD]
08/31/2019​
[/TD]
[TD]
07/31/2019​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
06/30/2019​
[/TD]
[TD]
07/31/2019​
[/TD]
[TD]
05/31/2020​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
4​
[/TD]
[TD]
Welder​
[/TD]
[TD]
05/31/2020​
[/TD]
[TD]
06/30/2020​
[/TD]
[TD]
06/30/2020​
[/TD]
[/TR]
</tbody>[/TABLE]

Formula in E2 copied down (same as my previous post)
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Questions
What Excel version are you using?
What you mean by "I couldn't get your formula to work."? An error, wrong results?

M.
 
Last edited:
Upvote 0
Marcelo Branco said:
Yes, I fully understood that column E only shows the desired results.
Have you tested the formula? It worked perfectly for me


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Employee ID​
[/TD]
[TD]
Worker Type​
[/TD]
[TD]
Contract Expiry Date​
[/TD]
[TD]
Current Demob Date​
[/TD]
[TD]
Desired Demob Date​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
08/31/2019​
[/TD]
[TD]
05/31/2020​
[/TD]
[TD]
08/31/2019​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
07/31/2019​
[/TD]
[TD]
08/31/2019​
[/TD]
[TD]
07/31/2019​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
Electrician​
[/TD]
[TD]
06/30/2019​
[/TD]
[TD]
07/31/2019​
[/TD]
[TD]
05/31/2020​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
4​
[/TD]
[TD]
Welder​
[/TD]
[TD]
05/31/2020​
[/TD]
[TD]
06/30/2020​
[/TD]
[TD]
06/30/2020​
[/TD]
[/TR]
</tbody>[/TABLE]

Formula in E2 copied down (same as my previous post)
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Questions
What Excel version are you using?
What you mean by "I couldn't get your formula to work."? An error, wrong results?

M.
Click to expand...

I pasted the formula into E2 and copied down.
When I say it didn't work it is because I am getting a #NAME ? error
I'm running Excel 2007 but will try this in a few hours on Office 365 when I get to work.

***Update: I've just tried it on my work laptop and it works!
It must have been the older version of Excel. I will test it out further with some more data
Thanks very much Marcelo!
 
Last edited:
Upvote 0
The AGGREGATE function is not available in Excel 2007, so try

Array formula in E2 copied down
=SMALL(IF(B$2:B$5=B2,IF(D$2:D$5>=C2,IF(ISNA(MATCH(IF(B$2:B$5=B2,D$2:D$5),E$1:E1,0)),D$2:D$5))),1)
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0
mojo707 said:
***Update: I've just tried it on my work laptop and it works!
It must have been the older version of Excel. I will test it out further with some more data
Thanks very much Marcelo!
Click to expand...

You are welcome. Glad to help.

M.
 
Upvote 0
Marcelo, is there any reason why this would be limited to only 6 rows? So far I've used sample data, but the real data set will be around 8000 rows. I have noticed that when I input data below row 6 the formula returns an error #NUM !
 
Upvote 0
You should adjust the ranges in the formula, changing:
B2:B5 to B2:Bn
D2:D5 to D2:Dn
where n is last row with data.

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
120
Peter_SSs
Peter_SSs
M
  • Question Question
How to sum cells in a column that match an End of Month date in an adjacent column
Replies
7
Views
261
Peter_SSs
Peter_SSs
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
A
  • Question Question
SUMIFS
Replies
1
Views
315
Rijnsent
Rijnsent

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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