I need to find the first occurrence of any date in column D, so that I can count the number of cells between row 1 and the date's row. The column might look like:
1 A
2 F
3 123
4 1-1-14
The distance from row 1 to row 4 where the first Date lies is 3. I need that number. I'm using this in VBA, but had a feeling Excel would have a built in function for it.
1 ?????? i think you've missed a bit on your post
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I pressed enter before I had finished, but I made a quick edit. Not quick enough for you though.
i think youll need code as
123 is a valid date number
02/05/1900
but with a helper column you could probably use
=AND(ISNUMBER(A1),LEFT(CELL("format",A1))="d") then match that column for true
index(a1:a10,match(true,b1:b10,0))
Last edited by martindwilson; 03-14-2014 at 10:23 AM.
I can probably get rid of any numbers above the date column that could be recognized as dates, actually. So, if that were the case...?
=iferror(index(a2:a100,match(true,index(isnumber(a2:a100),0),0)),"")
I meant that I could get rid of any numbers that could be construed as dates, but there still could be numbers. Is there something similar to ISNUMBER for dates? Also, I need to count the cells between the first date and the top row.Originally Posted by martindwilson
nope all numbers from 0 to 2958465
could potentially be dates
but if your max number is less than the lowest date you are likely to have
=IFERROR(INDEX(A2:A100,MATCH(1,INDEX(ISNUMBER(A2:A100)*(A2:A100>36526),0),0)),"") would find any date greater than 1/1/2000 which = 36526
if you just want the row then leave off the outer index
=IFERROR(MATCH(1,INDEX(ISNUMBER(A2:A100)*(A2:A100>36526),0),0),"")
Last edited by martindwilson; 03-14-2014 at 12:44 PM.
What the range of numbers could be? and range of dates?
I mean if the date range possibly is from 1/1/2000 - 1/1/2020, equal from 36526 to 43841, and the number range is out of date range, we possibly take that condition to find the date.
Quang PT
The number range is only going to be 1-10 for the time being and the date range is calendar year 2012 - 2015.
well post #8 solves that you just have to make sure no number above the date and in your case since you are using 1 to 10 just test the vale in the cell is > than 10 and is a number
So it quite the same idea with martin's:
=IFERROR(INDEX(A1:A5,MATCH(1,INDEX(1/(ISNUMBER(A1:A5)*(A1:A5)>=40909),),0)),"")
With 40909 is 1st-Jan-2012
heres a working sheet
Oh, it is enough with this only
=MATCH(1,INDEX(1/(ISNUMBER(A1:A5)*(A1:A5)>=40909),),0)
Thanks to the both of you. Everything working great!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks