Find Last Date Value in Excel 2013?

Question

I have from B1 and onward(C1,D1,etc), I want to find the last date that is filled in. How can I do this in excel?

Answer 1

Your question is not clear.

1. If you are looking for a maximum date in your range: In any any free and empty cell that you could use for the result type: =MAX(B1:X1) or use whatever your dates range is. This will give you the last (maximum) date.

2. If you are looking for the last cell that has a date in it and the next cell after it is empty then you could write a formula in cell B2: =IF(F1 = "","0",COLUMN(B1)) and then copy this formula below the dates row.
   Now, if the cell above has a date in it the formula will give you a column number which is larger that 1 (because you are starting in column 2 - "B"). If the cell is empty the formula will result in 0. Now you can find MAX(B2:X2) and this will be the number of the last column in row 1 that is not blank that is the last column with a date.

Hope that one of these 2 answers is what you are looking for!

Answer 2

This formula:

=MATCH(TODAY()+10000,B1:K1,1)

gives us the position (the column number + 1, because we are starting at column B) of the last date in the row. TODAY()+10000 just gives us a date distant into the future. Rather than using TODAY() you could use DATE(2013,1,1)+10000, or even just a v. large number!

This formula:

=INDEX(B1:K1,MATCH(TODAY()+10000,B1:K1,1))

extends this idea to return the date from the last cell containing a date.

Note If the row also contains large numbers which aren't actually dates, then it is a little more complicated.