Finding start & end dates?

haw

Board Regular
C1 through Z1 contain dates: 9/1/05, 9/8/05, 9/15/05, etc.
C2 through Z10 contain numbers between 0 and 40 (or are empty)

I need a formula in B2 (that I can then copy down through B10) that will:
1) find the first cell containing a positive number in the range C2:Z2
2) return the value from the corresponding cell in row 1, above that cell.

If C1, D1, E1 = 9/1, 9/8, 9/15,
and C2, D2, E2 = 0, 10, 10
Then I need the result to be the value (date) 9/8.

I also need the same type of formula to find the date for the LAST cell containing a number.

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi,

Try:

=INDEX(\$C\$1:\$Z\$1,MATCH(TRUE,(C2:Z2>0),0))
Confirmed with Ctrl + shift + enter.

and

=LOOKUP(9.99999999999999E+307,C2:Z2,\$C\$1:\$Z\$1)
normal enter

variation on the theme:

=INDEX(\$C\$1:\$Z\$1,,MIN(IF(\$C2:\$Z2>0,COLUMN(\$C2:\$Z2)-COLUMN(\$C\$2)+1)))

entered with control + shift +_ enter. change min() to max() for the last entry

A clarification:

My second formula would give you the last position holding ANY number as that is how I interpreted your query. (Different from Paddy)

If that is the right interpretation

=LOOKUP(2,1/(C2:Z2>0),\$C\$1:\$Z\$1)

All three of those options seem to work fine for finding the END DATE ... but I cannot figure out how to convert them to find the corresponding START DATE. Would still like some help on this.

If my request is not clear, I've pasted below a sample of what the end result I'm looking for. Thanks.
Book1
ABCDEFG
1StartDateEndDate11/711/1411/2111/2812/5
211/14/0511/28/05557
311/21/0512/05/05101010
411/14/0512/05/053333
511/14/0511/21/0533
Sample

The first formula that Fairwinds posted should work for you in A2, confirmed with CTRL+SHIFT+ENTER, alternatively you can use this formula which only requires ENTER

=INDEX(\$C\$1:\$Z\$1,MATCH(TRUE,INDEX((C2:Z2>0),0),0))

This picks the date associated with the first value above zero, i.e. if zero is shown the date is not picked

"The first formula that Fairwinds posted should work for you in A2,"

& my doesn't ?!?

OK. I've tried all these suggestions ... and it's clear that I'm missing the significance of the CNTL-SHIFT-ENTER comments. For the suggestions that reference CNTL-SHFT-ENTER ... if I just copy/paste the formulas from this thread into a cell in my sheet, I get #Value! errors.

The formulas that say they work with "simple ENTER" I can cut/paste and they work, no problem.

What does "confirmed with CNTL-SHIFT-ENTER" mean?

review the excel help file entry for "About array formulas and how to enter them"

"The first formula that Fairwinds posted should work for you in A2,"

& my doesn't ?!?

Replies
16
Views
475
Replies
10
Views
603
Replies
2
Views
165
Replies
1
Views
146
Replies
6
Views
464

1,203,052
Messages
6,053,234
Members
444,648
Latest member
sinkuan85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back