Index & Match for finding start & end dates based on contents in first & last cells in rows

[Mickdelo]

Dear All,

I am trying to come out with a formula that will assist in generating the start date and end date per row for different positions.
Try using the Index & Match formula but there is a problem with the Match type when using with numbers less than 1.

Formula used in typical row in cell C7 to generate Early Start date is: =IFERROR(IF(SUM(E7:M7)=0,0,INDEX(E$3:M$3,1,MATCH(1,E7:M7,0))),IF(SUM(E7:M7)=0,0,INDEX(E$3:M$3,1,MATCH(1,E7:M7,1))))
Formula used in typical row in cell E7 to generate Early Finish date is:
=IF(SUM(E7:M7)=0,0,INDEX(E$4:M$4,1,MATCH(1,E7:M7,1)))

See below sample where date generated in Row 7 Cell C7 is working i.e. it generates the Early Start as being 01-Nov-11 because the first Cell in Row 7 that has a 1 in it corresponds with the 01-Nov-11 date in Row 3.
Similarly Early Finish is correct as it is picking up the last cell with a 1 in it in Row 7 and returning the corresponding date in Row 4 i.e. 31-Mar-12.
However the formula are not working for cells with numbers using number less than 1 - see Cells C8 and D8 where C8 should be 01-Nov-11 but is showing 01-Apr-12. think this is because some of the numbers in cells are less than 1 e.g. 0.3
Any solutions to generate correct dates based on first cell and last cell entries would be much appreciated.

1	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	ITEM	Activity	Early	Early	M1	M2	M3	M4	M5	M6	M7	M8	M9	Total
3			Start	Finish	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12	Mths
4					Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Apr-12	May-12
5
6		Management				1.00	2.40	4.00	4.00	4.50	1.30	0.80		18.00
7		Project Manager	1-Nov-11	31-Mar-12			1.00	1.00	1.00	1.00	1.00			5.00
8		Engineer	1-Apr-12	30-Apr-12			0.30	0.30	0.30	0.30	0.30	0.30		1.80
9		Doctor	1-Dec-11	29-Feb-12				1.00	1.00	1.00				3.00
10		Nurse	1-Dec-11	29-Feb-12			0.40	1.00	1.00	1.00				3.40
11		Assistant	1-Feb-12	30-Apr-12			0.50	0.50	0.50	1.00		0.50		3.00
12		Driver	1-Oct-11	29-Feb-12		1.00	0.20	0.20	0.20	0.20				1.80
13

<tbody>
</tbody>

The sample above didnt come out very well; not sure if there is a better tool to use than Mr. Excel HTML Maker?

thanks,
Mickdelo

 

[keith101]

You need a different type of formula to find first and last non-zero values. Try

=IFERROR(INDEX($E$4:$M$4,MATCH(TRUE,E7:M7<>0,0)),"") for the early start and
=IFERROR(INDEX($E$4:$M$4,MATCH(TRUE,E7:M7<>0,1)),"") for the late start
and both should be entered as array formulae using CTRL-SHIFT-ENTER
Hope this helps
Keith