Oldest date with day 1 on column

[mark692]

hello is this possible on excel formula giving the date of the oldest date with day 1 on colum

example

12-6-17

2-7-16

8-2-17

3-1-17

12-26-17

6-1-17

<tbody>
</tbody>

on the given table the date that the formula that i need will find 3-1-17 because it is the oldest date with day 1,, thanks

 

[FormR]

the formula that i need will find 3-1-17 because it is the oldest date with day 1

Hi, isn't 6-1-17 older? Or am I not understanding correctly.

 

[mark692]

Hi, isn't 6-1-17 older? Or am I not understanding correctly.

no sir 6-1-17 is june 1 2017 , 3-1-17 is march 1 2017 so march is the oldest and june is the latest

 

[FormR]

Understood - here is one option you can try:

Excel 2013/2016
	A	B	C
1	12/06/2017		03/01/2017
2	02/07/2016
3	08/02/2017
4	03/01/2017
5	12/26/2017
6	06/01/2017
Sheet1

Cell Formulas
Range	Formula
C1	=AGGREGATE(15,6,A1:A6/(DAY(A1:A6)=1),1)

 

[mark692]

Understood - here is one option you can try:

Excel 2013/2016

	A	B	C
1	12/06/2017		03/01/2017
2	02/07/2016
3	08/02/2017
4	03/01/2017
5	12/26/2017
6	06/01/2017

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =AGGREGATE(15,6,A1:A6/(DAY(A1:A6)=1),1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

big thanks sir!