Returning the location of the first occurrence of a date in a range

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I am looking for some guidance in writing an array
formula that will identify the cell reference of the
First Occurrence of a date in a given row, going right to
left.

In row 2, for example, assume the following entries have
been inputted in columns A - D, respectively:

Ice cream
1/19/2004
$1,269
4/23/2003

Using the following array formula, I can easily determine
the cell reference of the Latest Date ($B$2):

{=ADDRESS(MAX(IF(A2:D2=MAX(A2:D2),ROW(A2:D2),"")),MAX(IF
(A2:D2=MAX(A2:D2),COLUMN(A2:D2),"")))}

but I still can't figure out how to determine the cell
reference of the First Occurrence of a Date (going right
to left) (which would be $D$2).

Any help would be greatly appreciated.

Thanks.
 
I am looking for some guidance in writing an array
formula that will identify the cell reference of the
First Occurrence of a date in a given row, going right to
left.

In row 2, for example, assume the following entries have
been inputted in columns A - D, respectively:

Ice cream
1/19/2004
$1,269
4/23/2003
Click to expand...
...

Interesting example. If the dollar value were > 38,000 (which may not be a
problem for you), then the dollar value would be the largest numeric value in
the range, and there'd be no easy way to find the latest date either L-to-R or
R-to-L. The problem is that dates are numbers, and in worksheets they can't be
distinguished from any other numbers by any built-in functions.

If dates were the only numeric values in a range, then you could find the
rightmost one using the array formula

=INDEX(A1:D1,MATCH(MAX(IF(ISNUMBER(A1:D1),COLUMN(A1:D1))),
IF(ISNUMBER(A1:D1),COLUMN(A1:D1)),0))

Note that converting this into a cell address is easier done using

=CELL("Address",INDEX(A1:D1,MATCH(MAX(IF(ISNUMBER(A1:D1),COLUMN(A1:D1))),
IF(ISNUMBER(A1:D1),COLUMN(A1:D1)),0)))


If you could have non-date numeric values in the range that could appear to the
right of the rightmost date, then you'll need a user-defined function. Is that
acceptable?
 
Thanks for the info!

I should have been more clear in my message. The good
news is that the dollar value will never be > 37,987 (the
serial number for 1/1/2004). The bad news is that there
may be instances where a non-date numeric value is in a
column to the right of a column containing a date.

If a user-defined function is the only way to solve this
problem, then I would greatly appreciate your help in
this area.

Thanks again!
 
Bob said:
If a user-defined function is the only way to solve this
problem, then I would greatly appreciate your help in
this area.
Click to expand...
....

In its most simplistic form,

Function findrightmostdate(r As Range) As Long
For findrightmostdate = r.Columns.Count To 1 Step -1
If IsDate(r.Cells(1, findrightmostdate).Value) Then Exit For
Next findrightmostdate
End Function
 
Thanks!!!

I hate to ask, but given my very limited knowledge of
VBA, could you please tell me how to modify your user-
defined function so that it returns the cell address
rather than the column number?

Thanks again.

Bob
 
One way:

Option Explicit
Function findrightmostdate(r As Range) As Variant
Dim iCtr As Long
Dim myCell As Range

findrightmostdate = CVErr(xlErrRef)

For iCtr = r.Columns.Count To 1 Step -1
Set myCell = r.Cells(1, iCtr)
If IsDate(myCell.Value) Then
findrightmostdate = myCell.Address(0, 0) '(1,1) if you want $'s
Exit For
End If
Next iCtr

End Function
 
Thanks again!!! It works beautifully!
-----Original Message-----
One way:

Option Explicit
Function findrightmostdate(r As Range) As Variant
Dim iCtr As Long
Dim myCell As Range

findrightmostdate = CVErr(xlErrRef)

For iCtr = r.Columns.Count To 1 Step -1
Set myCell = r.Cells(1, iCtr)
If IsDate(myCell.Value) Then
findrightmostdate = myCell.Address(0, 0) '(1,1) if you want $'s
Exit For
End If
Next iCtr

End Function




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
One way: ...
Function findrightmostdate(r As Range) As Variant ...
findrightmostdate = myCell.Address(0, 0) '(1,1) if you want $'s
Click to expand...
...

OK, but a udf returning the index in the row could be used to give the cell
address with

ADDRESS(ROW(r),findrightmostdate(r))

and it'd be much easier to change relative/absolute on the fly. Given a udf
returning a cell address as text, the inverse of returning the cell's column
index in the range would be

COLUMN(INDIRECT(findrightmostdate(r)))-CELL("Col",r)+1

Probably a nonissue for the OP, but it's generally easier to work with indices
than addresses.
 
Back
Top