Need HELP FORMULA for dates

[Louise_douglass]

Hi there,

I am looking for a formula that will tell me the start and end date in excel based off whether the cells are populated.

so in row 1 I have the dates
Below I have filled in 1's where there will be work for that week.

I want to know when the 1's start and when the 1's finish based off the dates that are in ROW 1.

Need help urgently!! Many thanks

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	01/06/2014	02/06/2014	03/06/2014	04/06/2014	05/06/2014	06/06/2014	07/06/2014	08/06/2014	09/06/2014	10/06/2014
2			1	1	1
3	Start	03/06/2014
4	End	05/06/2014
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX(A1:J1,MATCH(1,A2:J2,FALSE))
B4	=LOOKUP(2,1/(A2:J2=1),A1:J1)

 

[ww4612]

you can use sorting to sort the data/time field. i am not sure if that is what you need

 

[bbott]

Another solution if you want it in one cell as a date range:

=TEXT(INDEX(1:2,1,MATCH(1,2:2,0)),"MM/DD/YYYY")&" - "&TEXT(INDEX(1:2,1,MATCH(2,2:2,1)),"MM/DD/YYYY")

 

[Louise_douglass]

Thank you! think this is almost there. not all of the cells are populated with 1 - sorry that was a bad example - they are totals, so I need the formula to allow for any number but to recognise that its populated i.e. return date.

 

[Andrew Poulsom]

My formulas will work provided that at least on cell contains 1. Entries of 1 don't need to be contiguous.

 

[Louise_douglass]

my table looks like the following:
30-dec 6 Jan 13 Jan 20 Jan 27 Jan 3 Feb 10 Feb
0 34 40 150 134 120 212



Imagine that there are more rows with similar totals just at different dates...So what I need is a formula that is saying the project starts 6-Jan and ends 10 feb ...The end date can shift...

Does this help for finding the right formula?

Thanks for all of your help!!

 

[Andrew Poulsom]

You can't post an image like that. Copy your data and paste it into a reply.

 

[Louise_douglass]

30-Dec-13

6-Jan-14

13-Jan-14

20-Jan-14

27-Jan-14

3-Feb-14

10-Feb-14

17-Feb-14

24-Feb-14

3-Mar-14

10-Mar-14

17-Mar-14

24-Mar-14

31-Mar-14

7-Apr-14

156

165

167

176

182

189

196

205

209

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" span="3" width="110"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="7" width="70"> <col style="width:48pt" span="5" width="64"> </colgroup><tbody>
</tbody>

 

[Louise_douglass]

so this is the example where project start date is 6-Jan-14 End date:3-Mar-14

Thanks!