1

I current has an excel table filled with dates that looks something like this, but with more columns and much more rows.

2012-01-01   2013-02-04  (empty cell)   2017-01-01   (empty cell)
2011-02-01   2013-02-20   2014-05-05   (empty cell)   2016-03-04
...

How can I obtain the latest date from the columns? For example

2017-01-01
2016-03-04
...

I have tried sorting them using latest to earliest but the sorted data does not return what I wanted. And it sorted my entire table's date instead of sorting them row by row.

I have tried methods from here, which suggests me using =INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) but I could not make sense of the function nor could I make my table produce the latest date in another cell. Please suggest me any way of obtaining a row of the the latest date using excel formula preferably. (If it is not possible with excel only, please suggest me some alternatives.)

Community
  • 1
  • 1
DunnoHowToCode
  • 531
  • 1
  • 4
  • 14

2 Answers2

2

If the data is sequential like your example, simply use the Max formula:

=Max(1:1)
=Max(2:2)

for each row.

Here is the usage with your sample data:

enter image description here

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
1

A date is a number and you can get the column index of the last number with,

=match(1e99, 1:1)

So to get the last date in row 1 then

=index(1:1, match(1e99, 1:1))
  • The full row reference (e.g. `1:1` ) may have to be restricted to something like `A1:Z1` if you plan to put these formulas on the same row in the same worksheet. Using a full row reference will result in a circular reference since the formula contains a range that includes itself. –  Jul 15 '16 at 04:02