Return first and last dates of occupancy

[track29]

COLUMN B4:B99 are room numbers
ROW E3:N3 are dates
COLUMN C4:C99 has "Gain Control" header
COLUMN D4:D99 has "Release Control" header


I have a BeforeDoubleClick VBA that populates a cell in the grid, indicating that room belongs to that client on that date

Example: date range in E3:N3 is 3/31/16 to 4/9/16
Double Click three cells in a row for a room indicates that room belongs to that client for those three days

I would like for Gain Control and Release Control for that room to populate the first and last date of occupancy for that room

Stumped in SeattleAttachment 425333

[Glenn Kennedy]

Hi there. First Date:

=IFERROR(INDEX($E2:$N2,MATCH(TRUE,INDEX((E4:N4<>0),0),0)),"")

Second date:

=IFERROR(LOOKUP(2,1/($E4:$N4<>""),$E$2:$N$2),"")

as in the attached file. You many need to adjust the ranges to suit. BtW, the attachment didn't open for me.

[track29]

Awesome. Thanks much. Adjusted Absolute-ne$$ of ref in First Date then copied down. Did the trick.

[Glenn Kennedy]

You're welcome.... and thanks for the Rep.