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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12-6-17

[/TD]
[/TR]
[TR]
[TD]2-7-16[/TD]
[/TR]
[TR]
[TD]8-2-17[/TD]
[/TR]
[TR]
[TD]3-1-17[/TD]
[/TR]
[TR]
[TD]12-26-17[/TD]
[/TR]
[TR]
[TD]6-1-17[/TD]
[/TR]
</tbody>[/TABLE]


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
12/06/2017
02/07/2016
08/02/2017
03/01/2017
12/26/2017
06/01/2017

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]03/01/2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=AGGREGATE(15,6,A1:A6/(DAY(A1:A6)=1),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

big thanks sir!