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

[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(A22=MAX(A22),ROW(A22),"")),MAX(IF
(A22=MAX(A22),COLUMN(A22),"")))}

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.

 

[Harlan Grove]

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

...

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(A11,MATCH(MAX(IF(ISNUMBER(A11),COLUMN(A11))),
IF(ISNUMBER(A11),COLUMN(A11)),0))

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

=CELL("Address",INDEX(A11,MATCH(MAX(IF(ISNUMBER(A11),COLUMN(A11))),
IF(ISNUMBER(A11),COLUMN(A11)),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?

 

[Bob]

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!

 

[Harlan Grove]

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

....

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

 

[Bob]

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

 

[Dave Peterson]

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

 

[Bob]

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)
.

 

[Harlan Grove]

One way: ...
Function findrightmostdate(r As Range) As Variant ...
findrightmostdate = myCell.Address(0, 0) '(1,1) if you want $'s

...

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.