How to find the first cell in a row with a date

[pelesl]

I have a table which, across the top, has different cell titles, say "Category" in A1, "Budget" in B1, and C1 through F1 are date values.

I need a formula which will return "C1", because it's the left-most cell in the first row which contains a date.

I know I can find a date with ISNUMBER or the TYPE functions. But I cannot get TYPE to work on an array.

Honestly, I can't even understand the behavior of TYPE. Let's say B1 contains a string, and B2:B8 contain numbers.

If I select one cell and do TYPE(B1:B8) and press enter, I get "2", because B1 contains text.

If I change B1 to a number, the TYPE cell returns "16", which means error. Wha....?

If I instead press CTRL+SHIFT+ENTER on the TYPE cell, I get "64", which is the array type. So obviously this is not doing what I want it to do (which is to run the function TYPE on each of the cells B1 through B8)

With ISNUMBER, I can get the array functionality, but I want to find the *first* numerical cell, not if each cell is a number.

[DonkeyOte]

You can use a MATCH Array or use a MATCH with embedded INDEX

=MATCH(TRUE,INDEX(ISNUMBER($B$1:$B$8),0),0)

Or conversely if the number is always the first value after the last text value you could possibly use a MATCH construct along the lines of:

=1+MATCH(REPT("Z",255),$B$1:$B$8)

but again the above assumes no blanks interspersed between text & number and no text after numbers (ie a contiguous listing of text n times, numerics n times)

[Palmetto]

C1 through F1 are date values.

I need a formula which will return "C1", because it's the left-most cell in the first row which contains a date.

Your explanation lacks clarity. What do you mean you want to return C1?
If it is always the first cell on the row to contain a date then you can directly reference the cell in your formula.

It would help if you clearly explained what you are trying to accomplish within the context of the workbook.

[pelesl]

You can use a MATCH Array or use a MATCH with embedded INDEX

=MATCH(TRUE,INDEX(ISNUMBER($B$1:$B$8),0),0)

Thanks. This did exactly what I needed.

Synthesizing such formula statements seems to be quite a challenge. I hope I can learn a bit at least from examples like these.

[pelesl]

Thanks. This did exactly what I needed.

How do I mark threads as solved? And is there still a mechanism for rating responses?

[teylyn]

To acknowledge a post, click the scales icon in the gray title bar of the post, next to the alert icon.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save