Start and End date

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I have two columns column A is formatted with the date for the year column b
has data. I would like at the top of the page to display the date that data
starts to show and the date that data stops for the year.
Thanks for the assist

Example:
A B
01/01/08
01/02/08 1
01/03/08 1
01/04/08
01/05/08 1
01/06/08
01/07/08 1
01/08/08 1
01/09/08
01/10/08

Result
Start 01/02/08
End 01/08/08
 
=INDEX(A:A,MIN(IF(B1:B20=1,ROW(A1:A20))))

and

=INDEX(A:A,MAX(IF(B1:B20=1,ROW(A1:A20))))

which are array formulae, they should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Try these:

For start, an *array* formula:

=INDEX(A1:A10,MATCH(TRUE,(B1:B10<>""),0))

For end, a regular entry formula:

=INDEX(A1:A10,MATCH(99^99,B1:B10))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have two columns column A is formatted with the date for the year column b
has data. I would like at the top of the page to display the date that data
starts to show and the date that data stops for the year.
Thanks for the assist

Example:
A B
01/01/08
01/02/08 1
01/03/08 1
01/04/08
01/05/08 1
01/06/08
01/07/08 1
01/08/08 1
01/09/08
01/10/08

Result
Start 01/02/08
End 01/08/08
 
Back
Top