Oldest Date

[Johnboy28]

Hi, I need a formula that can tell me the oldest date based on the Code? Thank you

Code	Production Date	Oldest Date
24111	1/12/2024
24111	11/11/2023
24245	2/02/2024
24111	10/01/2024
24245	5/02/2024
21111	2/05/2024
60011	1/02/2024

 

[KRice]

I'm not certain about what is meant by "oldest" date. This returns the earliest date. If you want the latest date, change SMALL to LARGE.

MrExcel_20240107.xlsx
	A	B	C
1	Code	Production Date	Oldest Date
2	24111	1/12/2024	11/11/2023
3	24111	11/11/2023	11/11/2023
4	24245	2/2/2024	2/2/2024
5	24111	10/1/2024	11/11/2023
6	24245	5/2/2024	2/2/2024
7	21111	2/5/2024	2/5/2024
8	60011	1/2/2024	1/2/2024
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=SMALL(FILTER($B$2:$B$8,$A$2:$A$8=A2),1)

 

[Johnboy28]

=SMALL(FILTER($B$2:$B$8,$A$2:$A$8=A2),1)

Great - Thank you.

 

[SunnyAlv]

Another option

=SORTBY(A2:B8,A2:A8,1)

 

[Johnboy28]

Thanks again. I used =SMALL(FILTER($B$2:$B$8,$A$2:$A$8=A2),1)

 

[KRice]

You're welcome...glad to help.

@SunnyAlv, a quick side note...Your formula sorts by Code # but does not return the earliest date for each Code #...rather, it returns the original date associated with that particular item. There is some convenience in having a spilling formula: one idea is shown below in D2.

MrExcel_20240107.xlsx
	A	B	C	D
1	Code	Production Date	Oldest Date	Earliest Date (spilling)
2	24111	1/12/2024	11/11/2023	11/11/2023
3	24111	11/11/2023	11/11/2023	11/11/2023
4	24245	2/2/2024	2/2/2024	2/2/2024
5	24111	10/1/2024	11/11/2023	11/11/2023
6	24245	5/2/2024	2/2/2024	2/2/2024
7	21111	2/5/2024	2/5/2024	2/5/2024
8	60011	1/2/2024	1/2/2024	1/2/2024
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=BYROW($A$2:$A$8,LAMBDA(r,SMALL(FILTER($B$2:$B$8,$A$2:$A$8=r),1)))
C2:C8	C2	=SMALL(FILTER($B$2:$B$8,$A$2:$A$8=A2),1)
Dynamic array formulas.

 

[RoryA]

You could also use MINIFS:

=MINIFS($B$2:$B$8,$A$2:$A$8,A2)

 

[KRice]

Nice… I hadn’t thought about that.

 

[RoryA]

There are always so many ways to do anything in Excel!