Find the position of a date.

[eduzs]

In column "A" I have texts, values, and one cell with a DATE.
I need to find in which row is there this date.
Is there a formula to do that?
Many thanks

 

[Marcelo Branco]

Could you show some examples along with expected results?

M.

 

[Rick Rothstein]

Could you show some examples along with expected results?

To follow up on Marcelo's question... show us actual values that could be in the cells (do not simplify things by using 1, 2, 3, etc. for you numbers unless those are your real numbers).

 

[eduzs]

Thanks, I have this kind of data in the column "A"

[TABLE="class: grid, width: 83"]
<tbody>[TR]
[TD="width: 83, align: right"]1,43[/TD]
[/TR]
[TR]
[TD="align: right"]50,14
[/TD]
[/TR]
[TR]
[TD]Vendido
[/TD]
[/TR]
[TR]
[TD]Suportes
[/TD]
[/TR]
[TR]
[TD]Resistências[/TD]
[/TR]
[TR]
[TD]Último Preço[/TD]
[/TR]
[TR]
[TD]Papel
[/TD]
[/TR]
[TR]
[TD="align: right"]13/09/2017
[/TD]
[/TR]
[TR]
[TD]MACD[/TD]
[/TR]
</tbody>[/TABLE]

So I need to retrieve this date "13/09/2017", or get the address where it is.

The date is not always the same, nor always in the same cell address in the 1500 rows sheet.

Thanks.

 

[Rick Rothstein]

Thanks, I have this kind of data in the column "A"

[TABLE="class: grid, width: 83"]
<tbody>[TR]
[TD="width: 83, align: right"]1,43[/TD]
[/TR]
[TR]
[TD="align: right"]50,14
[/TD]
[/TR]
[TR]
[TD]Vendido
[/TD]
[/TR]
[TR]
[TD]Suportes
[/TD]
[/TR]
[TR]
[TD]Resistências[/TD]
[/TR]
[TR]
[TD]Último Preço[/TD]
[/TR]
[TR]
[TD]Papel
[/TD]
[/TR]
[TR]
[TD="align: right"]13/09/2017
[/TD]
[/TR]
[TR]
[TD]MACD[/TD]
[/TR]
</tbody>[/TABLE]

So I need to retrieve this date "13/09/2017", or get the address where it is.

The date is not always the same, nor always in the same cell address in the 1500 rows sheet.

Give this formula a try (you will probably have to format the cell you put it in as a Date)...

=LOOKUP(9E+99,A:A)

 

[Marcelo Branco]

Or maybe this UDF (user Defined Function)

Function FindDate(r As Range)
    Dim rFound As Range
   
    Application.FindFormat.NumberFormat = "m/d/yyyy"
    Set rFound = r.Find(What:="*", SearchFormat:=True)
    If Not rFound Is Nothing Then
        FindDate = rFound.Row
    Else
        FindDate = "Not Found"
    End If
End Function

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td]

DateRow​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1,43​

[/td][td][/td][td]

14​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3,45​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

50,14​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Vendido​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

MACD​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Resistências​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Suportes​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Resistências​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Último Preço​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Último Preço​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Papel​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Nível IFR 14​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

13/09/2017​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

MACD​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in C2
=FindDate(A2:A15)

M.

 

[Rick Rothstein]

Give this formula a try (you will probably have to format the cell you put it in as a Date)...

=LOOKUP(9E+99,A:A)

Silly me ... a much simpler formula is available (you will still have to format the cell you put it in as a Date).

=MAX(A1:A15)

 

[Rick Rothstein]

Or maybe this UDF (user Defined Function)

Function FindDate(r As Range)
    Dim rFound As Range
   
    Application.FindFormat.NumberFormat = "m/d/yyyy"
    Set rFound = r.Find(What:="*", SearchFormat:=True)
    If Not rFound Is Nothing Then
        FindDate = rFound.Row
    Else
        FindDate = "Not Found"
    End If
End Function

Another way to write a UDF to return the row number...

Function FindDate(R As Range) As Long
  FindDate = Application.Match(Application.Max(R), R, 0)
End Function

 

[Marcelo Branco]

Silly me ... a much simpler formula is available (you will still have to format the cell you put it in as a Date).

=MAX(A1:A15)

Rick,
The stock market in Brazil is up. The IBOVESPA (general index as the Dow Jones in US) today stands at more than 70,000 points.
So your formula is a bit risky ...

Just kidding.
Of course you could not know that the data refer to stock quotes in Brazil - the text is in Portuguese.

M.

 

[eduzs]

Thanks Marcelo and Rick,
I understood that there is no way to directly verify the existence of a date, I will use one of the suggested solutions.