# Formula to show the earliest date from a range with criteria



## TruffleOil (Dec 16, 2022)

I have a set of data (see picture attached), where different projects have several dates, even within same month (A3:B16).  My goal is to build a ''calendar'' that will show Projects and their *first *date that will fit within the selected month.

E.g. in the picture: I want to see the projects that have dates that fall within January 2023 as per G3, but I don't want to see all their dates and repeated name of the projects. So for project A, which has multiple dates in January (B7:B10), I just want to see the first/earliest date from that project in January, which is 01/05/2023. Same applies to Project B.

Would appreciate if anyone could help to come up with a formula (maybe Index Match?) for D4 and E4!

Thank you!


----------



## JamesCanale (Dec 16, 2022)

This seems too cumbersome to me.
MrExcelPlayground14.xlsxABCDEFG2Project3A12/15/2022ProjectDate1/1/20234A12/22/2022A1/5/20235A12/29/2022B1/8/20236A1/5/20237A1/12/20238A1/19/20239A1/26/202310B1/8/202311B1/16/202312B7/6/202313B7/13/202314B7/20/202315B7/27/2023Sheet32Cell FormulasRangeFormulaE4:E5E4=MIN(IF(($A$3:$A$15=D4)*(MONTH($B$3:$B$15)=MONTH($G$3))*(YEAR($B$3:$B$15)=YEAR($G$3)),$B$3:$B$15,99999))


----------



## Sufiyan97 (Dec 16, 2022)

Another Option

Book3ABCDEFGHIJ12Source Data3Project NameDateProject NameDateJanuary20234A12/15/2022A1/5/20231/1/20231/31/20235A12/22/2022B1/8/20236A12/29/20227A1/5/20238A1/12/20239A1/19/202310A1/26/202311B1/8/202312B1/16/202313B7/16/202314B7/13/202315B7/20/202316B7/27/20231718Sheet1Cell FormulasRangeFormulaE4:E5E4=INDEX($B$4:$B$16,AGGREGATE(15,6,(ROW($B$4:$B$16)-ROW($B$4)+1)/($B$4:$B$16>=$H$4)/($B$4:$B$16<=$I$4)/($A$4:$A$16=$D4),1))


----------



## TruffleOil (Dec 16, 2022)

JamesCanale said:


> This seems too cumbersome to me.
> MrExcelPlayground14.xlsxABCDEFG2Project3A12/15/2022ProjectDate1/1/20234A12/22/2022A1/5/20235A12/29/2022B1/8/20236A1/5/20237A1/12/20238A1/19/20239A1/26/202310B1/8/202311B1/16/202312B7/6/202313B7/13/202314B7/20/202315B7/27/2023Sheet32Cell FormulasRangeFormulaE4:E5E4=MIN(IF(($A$3:$A$15=D4)*(MONTH($B$3:$B$15)=MONTH($G$3))*(YEAR($B$3:$B$15)=YEAR($G$3)),$B$3:$B$15,99999))[/RANt


Cell FormulasRangeFormula


Sufiyan97 said:


> Another Option
> 
> Book3ABCDEFGHIJ12Source Data3Project NameDateProject NameDateJanuary20234A12/15/2022A1/5/20231/1/20231/31/20235A12/22/2022B1/8/20236A12/29/20227A1/5/20238A1/12/20239A1/19/202310A1/26/202311B1/8/202312B1/16/202313B7/16/202314B7/13/202315B7/20/202316B7/27/20231718Sheet1Cell FormulasRangeFormulaE4:E5E4=INDEX($B$4:$B$16,AGGREGATE(15,6,(ROW($B$4:$B$16)-ROW($B$4)+1)/($B$4:$B$16>=$H$4)/($B$4:$B$16<=$I$4)/($A$4:$A$16=$D4),1))


thank you for replying! the formulas work great, but I would also like to have a formula that selects the projects (D4) based on the same criteria as the dates (E4). for instance, there might be other projects in the source data, such as Project C, D, E, etc. that do not have any January dates, so from a big data source, I wouldn't know which ones would fit within the January period, sorry if I didn't elaborate that in the post. in these formulas I tried, it already assumes that the dates belong to those projects, but realistically I don't know which projects should be there either


----------



## JamesCanale (Dec 16, 2022)

This is the part where I recommend excel 365.  It would make quick work of this whole thing.  Below is a bit of a mess.

MrExcelPlayground14.xlsxABCDEFG2Project3A12/15/2022ProjectDate1/1/20234A12/22/2022  5A12/29/2022B1/8/20236A1/5/2023A1/5/20237A1/12/2023  8A1/19/2023  9A1/26/2023  10B1/8/2023  11B1/16/2023  12B7/6/2023  13B7/13/2023  14B7/20/2023  15B7/27/2023  16C12/15/2022  17C2/2/2022  18C2/5/202319C2/10/2023Sheet32Cell FormulasRangeFormulaD4:D17D4=IFERROR(LOOKUP(2,1/(COUNTIF($D$3:D3,IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,""))=0),IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,"")),"")E4:E17E4=IFERROR(1/(1/(MIN(IF(($A$3:$A$19=D4)*(MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$B$3:$B$19,"")))),"")


----------



## TruffleOil (Dec 16, 2022)

JamesCanale said:


> This is the part where I recommend excel 365.  It would make quick work of this whole thing.  Below is a bit of a mess.
> 
> MrExcelPlayground14.xlsxABCDEFG2Project3A12/15/2022ProjectDate1/1/20234A12/22/2022  5A12/29/2022B1/8/20236A1/5/2023A1/5/20237A1/12/2023  8A1/19/2023  9A1/26/2023  10B1/8/2023  11B1/16/2023  12B7/6/2023  13B7/13/2023  14B7/20/2023  15B7/27/2023  16C12/15/2022  17C2/2/2022  18C2/5/202319C2/10/2023Sheet32Cell FormulasRangeFormulaD4:D17D4=IFERROR(LOOKUP(2,1/(COUNTIF($D$3:D3,IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,""))=0),IF((MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$A$3:$A$19,"")),"")E4:E17E4=IFERROR(1/(1/(MIN(IF(($A$3:$A$19=D4)*(MONTH($B$3:$B$19)=MONTH($G$3))*(YEAR($B$3:$B$19)=YEAR($G$3)),$B$3:$B$19,"")))),"")


oh wow it worked, thank you so much!! yes, I'm looking to update to office 365, it's much easier with formulas. what kind of formula would you use for office 365, out of curiosity?


----------



## JamesCanale (Dec 16, 2022)

In 365 - exactly one formula solves the whole thing.

MrExcelPlayground14.xlsxABCDE11/1/20232Project3A12/15/2022A1/5/20234A12/22/2022B1/8/20235A12/29/20226A1/5/20237A1/12/20238A1/19/20239A1/26/202310B1/8/202311B1/16/202312B7/6/202313B7/13/202314B7/20/202315B7/27/202316C12/15/202217C2/2/202218C2/5/202319C2/10/2023Sheet32Cell FormulasRangeFormulaD3:E4D3=LET(a,(FILTER(A3:B19,(MONTH(B3:B19)=MONTH(D1))*(YEAR(B3:B19)=YEAR(D1)))),b,SORT(SORT(a,2),1),c,UNIQUE(INDEX(a,,1)),d,XLOOKUP(c,INDEX(b,,1),INDEX(b,,2),,1),IF(ISODD(SEQUENCE(ROWS(d),2)),c,d))Dynamic array formulas.


----------



## Fluff (Dec 16, 2022)

Another option for 365
Fluff.xlsmABCDE101/01/20232Project3A15/12/2022A05/01/20234A22/12/2022B08/01/20235A29/12/20226A05/01/20237A12/01/20238A19/01/20239A26/01/202310B08/01/202311B16/01/202312B06/07/202313B13/07/202314B20/07/202315B27/07/202316C15/12/202217C02/02/202218C05/02/202319C10/02/202320DataCell FormulasRangeFormulaD3:E4D3=LET(u,UNIQUE(FILTER(A3:A100,(B3:B100>=D1)*(B3:B100<=EOMONTH(D1,0)))),HSTACK(u,MINIFS(B:B,A:A,u,B:B,">="&D1)))Dynamic array formulas.Named RangesNameRefers ToCells_FilterDatabase=Data!$B$1:$B$19D3


----------

