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

 

[bbott]

If your values always increase as the date increases....

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

 

[Andrew Poulsom]

A slight change to my formulas for numbers other than 1:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	30-Dec-13	06-Jan-14	13-Jan-14	20-Jan-14	27-Jan-14	03-Feb-14	10-Feb-14	17-Feb-14	24-Feb-14	03-Mar-14	10-Mar-14	17-Mar-14	24-Mar-14	31-Mar-14	07-Apr-14
2		156	165	167	176	182	189	196	205	209
3	Start	06-Jan-14
4	End	03-Mar-14
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX(A1:O1,MATCH(1,INDEX(--(A2:O2<>""),),FALSE))
B4	=LOOKUP(2,1/A2:O2,A1:O1)

 

[Louise_douglass]

Thank you!! This has worked. Really appreciate it!!