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

Mickdelo

New Member
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

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

Last edited:

Replies
3
Views
141
Replies
9
Views
152
Replies
15
Views
435
Replies
7
Views
165
Replies
4
Views
338

1,203,081
Messages
6,053,416
Members
444,662
Latest member
AaronPMH

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