Finding Row Header or 1st and last cell populated with Data

[gahooja]

I have a set of data which has dates in D4:AE4 starting from Jan 2011 to April 2013 underneath that i have data set of hours charged to a particular project. I am trying to see if there is a formula that i can use to determine the duration of the project based on what is populated in the rows below.

For Example

Row 4 - jan11, feb11, mar11, apr11, may11, june11
Proj X - 0 , 24, 40, 0, 0, 0
Proj Y - 20, 20, 20, 20, 0, 0
Proj Z - 0, 0, 0, 40, 40, 40


I should be able to get the following results from the data set:
Proj X, Start Date Feb 11 - End Date Mar 11
Proj Y, Start Date Jan 11 - End Date Apr 11
Proj Z, Start Date Apr 11 - End Date Jun 11

It could be 2 different formulas to determine start date which would lookup 1st date with row greater than zero and then End date which would show last cell with data greater than zero.
Thanks

 

[Aladin Akyurek]

Is Proj W - 0, 0, 15, 0, 15, 30 also possible?

 

[gahooja]

Yes, that would be possible as well.

 

[MrVillareal]

Hi try this,

Start Date = INDEX($D$4:$AE$4,MATCH(TRUE,INDEX(D5:AE5>0,0,0),0))
End Date = LOOKUP(TRUE,1/D5:AE5>0,$D$4:$AE$4)

 

[gahooja]

The start date works perfectly but the end date formula stops at the 1st non blank column. There are instances where for 1 month it goes to zero and then picks back up.

 

[MrVillareal]

but it works for me

Excel 2010
	C	D	E	F	G	H	I	J	K
4		28/05/2013	29/05/2013	30/05/2013	31/05/2013	01/06/2013	02/06/2013	03/06/2013	04/06/2013
5	a	0	4	0	0	4	4	4	4
6	b	8	0	0	8	8	8	8	0
7	c
8
9
10	Project	Start	End
11	a	29/05/2013	04/06/2013
12	b	28/05/2013	03/06/2013
Sheet1

Cell Formulas
Range	Formula
D11	=INDEX($D$4:$K$4,MATCH(TRUE,INDEX(D5:K5>0,0,0),0))
E11	=LOOKUP(TRUE,1/D5:K5>0,$D$4:$K$4)

 

[Rick Rothstein]

The start date works perfectly but the end date formula stops at the 1st non blank column. There are instances where for 1 month it goes to zero and then picks back up.

Both formulas work fine for me.

 

[Aladin Akyurek]

Yes, that would be possible as well.

Start date: Control+shift+enter, not just enter...

=INDEX(B1:G1,MATCH(TRUE,ISNUMBER(1/B2:G2),0))

End date: Just enter...

=LOOKUP(9.99999999999999E+307,1/B2:G2,B1:G1)

You can concatenate the results if so desired.

 

[gahooja]

If i do the 9.999 that seems to work for me.

Thanks for all the help.

 

[Aladin Akyurek]

If i do the 9.999 that seems to work for me.

Thanks for all the help.

You are welcome.