Return date based on month and year from data

[Sabakarp]

I have a set of data that consists of one column of dates and another column of values that corresponds to the date next to it. I would like to have a formula that you can search for the lowest date based on the month and year.

So if I have February and 2020 as the criteria, and the data is 2/1/2020, 2/1/2019, 2/2/2020, 2/1/2021

It would return 2/1/2020 and nothing else.

I would prefer not to have it as a VBA code as this formula will have to be done with every month on one sheet.

 

[Joe4]

Can you post a small sample of your data?
I would like to see not only the data section, but the criteria section.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Sabakarp]

Can you post a small sample of your data?
I would like to see not only the data section, but the criteria section.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

here is the data:

1/28/2018​

1/7/2018​

2/18/2018​

1/21/2018​

12/31/2017​

2/4/2018​

2/11/2018​

1/14/2018​

2/25/2018​

...​

here is what I want the data to turn into and the criteria would be the months in the first column and the year in the headers:

The idea is so that the boxes with the dates can changed based on the data provided, so if the data changes so do the boxes.

Before you think it's impossible with the years being hard entered, the first year is a formula that isolates year of the earliest date while the next years just add one to the previous. For example, "2017" is actually the formula: =year(Min(data)).

 

[Joe4]

It just looks like it is the list of Sundays for a whole year.
Do you really need to look/match them up, or can we just populate them all, knowing that you want to post all the Sundays for a given year?

 

[Sabakarp]

It just looks like it is the list of Sundays for a whole year.
Do you really need to look/match them up, or can we just populate them all, knowing that you want to post all the Sundays for a given year?

I guess what I really need is to automatically place the earliest date (in this case 12/31/2017) and have is stop once the data runs out or prevent earlier dates (like 12/24/2017) from showing up. Meaning that once the last date gets placed it won't continue to fill in dates.

The tricky part might be that, each box next to the month has to correspond to which week it is:

month	wk ending 1	wk ending 2	wk ending 3	wk ending 4	wk ending 5
DEC​	-​	-​	-​	-​	12/31​