I am running on panel project where I have to merge and match two dataset . My first dataset is a set of years and specific dates of start and end of period within a year and the second yearly country observations. Observations are yearly. In need to match the yearly observations of my second dataset to the longest duration in days starting from January 1st of each year i my first dataset . Below I paste some parts of the datasets in order to help you understand better. You can note that in some cases more than one period occurs. Fot those periods I need to mach to the longest duration as already said from the start of each year separately. Imagine that I need to do that for fifty years for around 45 countries and you get an idea how difficult and time consuming is doing that in excel in really Any ideas?

Dataset 1

Country OECD ts dm dm1 start of period End of period
Finland oecd member eu member 2006 24/6/03 19/4/07 Finland
Finland oecd member eu member 2007 24/6/03 19/4/07 Finland
Finland oecd member eu member 2007 39159 200703 19/4/07 22/6/10 Finland
Finland oecd member eu member 2008 19/4/07 22/6/10 Finland
Finland oecd member eu member 2009 19/4/07 22/6/10 Finland
Finland oecd member eu member 2010 19/4/07 22/6/10 Finland
Finland oecd member eu member 2010 22/6/10 22/6/11 Finland
Finland oecd member eu member 2011 22/6/10 22/6/11 Finland
Finland oecd member eu member 2011 40650 201104 22/6/11 25/3/14 Finland
Finland oecd member eu member 2012 22/6/11 25/3/14 Finland
Finland oecd member eu member 2013 22/6/11 25/3/14 Finland
Finland oecd member eu member 2014 22/6/11 25/3/14 Finland
Finland oecd member eu member 2014 25/3/14 24/6/14 Finland
Finland oecd member eu member 2014 24/6/14 26/9/14 Finland
Finland oecd member eu member 2014 97 26/9/14 29/5/15 Finland
Finland oecd member eu member 2015 42113 201504 29/5/15 31/12/18 Finland
Finland oecd member eu member 2015 29/5/15 31/12/18 Finland
Finland oecd member eu member 2016 29/5/15 31/12/18 Finland

Dataset 2

Country Country symbol year year code var1 var2 var3
Finland FIN 2006 YR2006 0,623874 2,002701 8,384
Finland FIN 2007 YR2007 1,566664 2,006751 7,719
Finland FIN 2008 YR2008 2,510666 2,010769 6,854
Finland FIN 2009 YR2009 4,065954 2,017309 6,369
Finland FIN 2010 YR2010 -9,2E-07 2 8,249
Finland FIN 2011 YR2011 1,184135 2,005112 8,394
Finland FIN 2012 YR2012 3,416808 2,014591 7,781
Finland FIN 2013 YR2013 2,808336 2,012028 7,689
Finland FIN 2014 YR2014 1,478286 2,006373 8,193
Finland FIN 2015 YR2015 1,041196 2,004498 8,663