Lookup date of first and last values in a range

[AXW]

In a line of data with dates above is there a way to
lookup the date that applies to the first and last value
in the line of data?
Thanks

 

[Jason Morin]

With dates in row 1 and data in row 2, for the first
value:

=INDEX(1:1,MIN(IF(LEN(2:2)>0,COLUMN(2:2))))

and the last value:

=INDEX(1:1,MIN(IF(LEN(2:2)>0,COLUMN(2:2))))

Each formula is an array formula, so you must press
<ctrl><shift><enter> for it to work. Format each formula
cell as date.

HTH
Jason
Atlanta, GA

 

[axw]

Thanks but I couldn't get it to work:

eg in this example when I typed your formula in cell A3:
={INDEX(B2:e2,MIN(IF(LEN(b3:e3)>2,COLUMN(b3:e3))))}
The ans was 31 Dec vs the ans I want is 31 Jan.

1 A B C D E
2 31 Dec 31 Jan 29Feb 31 Mar
3 31 Dec 2

am I doing something wrong?
Thanks

 

[Jason Morin]

Well, first, it should be >0 in the formula, not >2.
Also, this is an array formula. Once you insert the
formula, hold down the <ctrl> and <shift> keys and press
<enter>. Excel will automatically place {} around the
formula to indicate it is an array formula. Any time you
edit the formula, remember to array-enter so that the
brackets appear.

Jason

 

[Guest]

<enter> array formula. And sorry I had just put that 2

in as a test as I have many zeros in my cashflow. Using a
zero did not change the problem.
The only way I could get it to work was by pasting a
whole load of row dates (enough to match the no. of cost
rows), then all the rows of costs, THEN the same no. of
rows of formula's BELOW all these rows - so 10 rows of
dates, 10 rows of costs, then 10 rows (well 10 cells
vertically) of formulas. I had to do this as I don't
think pasting ranges into your formula worked ie I had to
just leave the formula as 1:1 and 2:2 ie referring to
whole rows rather than eg B1:E1 and B2:E2. This meant a)
the formulas had to be below the rows of costs rather
than adjacent and b)I had to paste multiple date rows in
order to be able to drag down the formula.
So it now works but am sure I took a wrong turn somewhere
as am sure you meant it to be much simpler and cleaner.