Solved: Search for date in range and return cell address

[lynnnow]

Hi,

I've got a range of dates and need to find the cell address for the 22nd day of the month.

The date is entered as m/d/yy and formatted to show as mmmm dd, yyyy (Note: The Text function is not used.)

I would like a formula to find the cell address. I can get the cell address with a macro, but don't want to go that route. The cell address is used in four other concatenated formulas that need the cell address.

Also, the dates start with the 23rd of the current month to the 22nd of the next month. So I need the cell address for the 22nd day. Also the date is entered as a heading for a set of 7 columns

Please help.

[Bob Phillips]

Try

=ADDRESS(MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,22),rng,0)+ROW(rng)-1,COLUMN(rng))

[lynnnow]

Hey XLD,

Using your suggestion, this is the formula:

=ADDRESS(MATCH(DATE(YEAR(B4),MONTH(B4)+1,22),B4:HD4,0)+ROW(B4:HD4)-1,COLUMN(B4:HD4))

But the result is $B$214, which is wrong.

The dates are horizontally arranged.

Kindly edit the formula.

Thanks in advance.

[Bob Phillips]

Try this

=ADDRESS(ROW($B$4:$HD$4),MATCH(DATE(YEAR($B$4),MONTH($B$4)+1,DAY($B$4)),$B$ 4:$HD$4,0)+COLUMN($B$4:$HD$4)-1)

[lynnnow]

Thanks XLD, I had to modify the formula some:

[VBA]=ADDRESS(ROW($B$4:$HD$4),MATCH(DATE(YEAR($B$4),MONTH($B$4)+1,DAY($B$4-1)),$B$4:$HD$4,0)+COLUMN($B$4:$HD$4)-1,4)[/VBA]

This works superb