Collect non empty cell before know value

[jomaor1]

Hello again fellow Excel addicts,
As shown in the attachment, I have a small table that is missing a "End" date, I've been trying to create a formula so my team doesn't have to manually type every date, after changing the Gantt area (Dates in the right).

asd.PNG

So I just need a formula on the "End" column to collect the next non-empty cell before "TST".

Note: the Start and Test days are known (I've created a formula already).
Edit: The empty spaces between dates and "TST" can vary.

Thank you, John.

[Special-K]

Seems to work, finds last number on a row (ignores TST)

=OFFSET(G3,0,MATCH(MAX(G3:R3)+1,G3:R3,1)-1)
and copy down the column

[KOKOSEK]

TST is date from D3?
Anyhow, try:

Please Login or Register  to view this content.

accept with Ctrl+Shift+Enter

[jomaor1]

Sorry, but it has to be before TST, because it can be other cell values after TST,
so my returned value has to be 27/2, or 27th of February.

Note that the "MAX" is returning 16, and adding one, and that makes zero sense to me

[jomaor1]

Yes, TST date is in D3

Didn't work, it returned a #N/A error.

Note: I need the value on O1 to be returned (27/2).

[KOKOSEK]

Misunderstood.
Put into C3:

Please Login or Register  to view this content.

accept with CSE.

[jomaor1]

Yes!! it worked flawlessly, thank you very much!

[KOKOSEK]

You welcome.
If you happy, please use thread tools and marked thread as SOLVED.