Merge 2 datasets by common id field AND 2 dates if they are within 365 days of each other

[LPSUCSF]

Hi,

I have 2 datasets that I would like to merge together. There is a common id field between the two but I need the two datasets to also align by date. There is no common date field. One dataset has a created date field and the second dataset has a completed date field. I would like to align the datasets by id and date if the 2 dates are within 365 days of each other. Is there a way to do this in Excel?

Thank you!

[#2]

@LPSUCSF -

You could merge/join the tables using Power Query. While in PQ, you can than evaluate the dates then filter out any that do not met the criteria. If you could provide a data sample we could help with a more detailed solution.

[#3]

@ChrisMendoza Thank you!

I've attached a sample of what I have. 

[#4]

@LPSUCSF -

Are these the results you're looking for?

[#5]

@ChrisMendoza Please see updated Excel file with desired merge results. I need the rows to align where the created date and completed date are with 365 days of each other. Thank you!

[#6]

@LPSUCSF -

Maybe this is close enough?

[#7]

@ChrisMendoza That's great! What did you do? Thank you!

[#8]

@LPSUCSF -

Data > Queries & Connections > Right-Click Edit to see what was done in the Query Editor.

In the Step 'Added Custom' I added IF ELSE logic as:

if Duration.Days([Dataset2.completeddate]-[createddate]) > 365 then null else if Duration.Days([Dataset2.completeddate]-[createddate]) <= 0 then null else Duration.Days([Dataset2.completeddate]-[createddate])

Producing:

Then just filtered out NULL.

[#9]

@ChrisMendoza Hi. I'm not familiar with Power Queries. Could you please provide a link to how you did this? I am unable to follow what you did. I don't see Queries and Connections in my Data tab. I see only Connections. Thank you.

[#10]

@LPSUCSF -

It's moved and renamed a few times. Which version of (Desktop) Excel are you using? 

[#11]

@ChrisMendoza I have Excel 2016

[#12]

@LPSUCSF - Looks like it's 'Show Queries' as shown in https://www.youtube.com/watch?v=zjnFKS9iXPs

[#13]

@ChrisMendoza I think I finally got it. Will need to do some data clean up. This was definitely faster than by hand.