SubscribeUse MAX to find latest date in a list [Quick tip]
Here is a quick tip that I learned while conducting training classes in Australia.
If you have several dates in a range and you want to find out what the latest date is, just use MAX, like:
=MAX(A1:A10) would give you the latest date.
A Question…,
Assuming you have some dates (not necessarily sorted) in column A, which formula finds the last date (not latest)?
Bonus question: What if there are some gaps (cells with no value)? How would you find the last date?
Go ahead and post your answers in comments. Or share your favorite formula to find latest date in a range.
PS: My Australian trip is over now. On a train from Melbourne to Sydney now and will be leaving to Vizag via Bangkok (and Hyderabad) early tomorrow morning. I am very happy how the whole thing went. More on this later next week.
PPS: More tips on dealing with date & time values in Excel
| ||||
|
| ||||
|
Leave a Reply
 |
Thermo-meter chart with Marker for Last Year Value | Extracting numbers from text in excel [Case study] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
33 Responses to “Use MAX to find latest date in a list [Quick tip]”
=INDEX(A:A,IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH(“*”,A:A,-1),IF(ISERROR(MATCH(“*”,A:A,-1)),MATCH(9.999999E+306,A:A),MAX(MATCH(9.999999E+306,A:A),MATCH(“*”,A:A,-1)))))
This seems to do it. You can use TEXT() function to format the date to your liking.
I just checked a simple small or min works – even with the gaps in between but ofcourse it has to be in excel date format.
Regards,
Rajib
Hi Raj
ur right just max or min should work, it need not be in date format..reason is date is ultimately number and min and max should give results
Last date with or without gaps:
=INDEX(A:A,MATCH(9E99,A:A))
Awsome!
What does the 9E99 do?
It’s simply a very large number. Stands for 9*10^99
I’m using it so that the MATCH function will look for the number closest to 9E99, aka the highest number aka the latest date. If we were looking for text, I’d use “zzz” to follow a similar pattern.
A date in Excel is just the number of days since 1 Jaunary 1900. If you can use an Excel function on a number, you can also use it on a date.
To see what is going on, just change the cell format from “date” to “number”. Hours, minutes and seconds are just decimal fractions of days.
Excel does not display dates before 1/1/1900 (I guess this is in order to avoid any complications with the various calendar reforms that have occurred throughout history).
{=MAX((MyRange<(TODAY()+1))*MyRange)}
Where MyRange is the the data. The curly braces indicate that you’ve CTRL-ALT-ENTERed the formula. It looks to me that you’ve got to have a contiguous range for it to work, but it handles spaces. If you want the last day before today, delete the “+1″.
I assumed that you were asking for the last date listed, with no regard to if is is the most or least current date. So this is really just a way of finding the last populated cell in a column.
{=MAX(IF(NOT(ISBLANK(A:A)),ROW(A:A),”"))}
From here you could bolt on an =Address() and a =indirect() if you waned to get the location or value.
{=INDIRECT(ADDRESS(MAX(IF(NOT(ISBLANK(A:A)),ROW(A:A),”")),1))}
Thanks for these challenges, Chandoo.
Ben
Select the range including empty cells and do a conditional formatting using top 10 and choose 1 in the dialog box making it only the highest number (latest date). It will highlight the latest date in the selected range.
THANK YOU!!!
Behold, a less than elegant solution:
{=INDIRECT(“A” & MAX((A:A>1)*ROW(A:A)))}
Alternatively, you can use “Index(a:a…” but I wanted to spice things up a bit
On the topic of Max dates, I use this particular array function quite frequently. It finds the latest date for a particular item. Almost like a ‘MaxIf’
Suppose you have a table of prices for 3 fruit which also contains historical prices:
E.g.
Date Fruit Price
01/01/12 Apple $1.00
01/01/12 Banana $3.00
01/01/12 Cantaloupe $4.00
03/10/12 Apple $1.35
04/11/12 Banana $2.80
06/12/12 Cantaloupe $4.05
The following formula will return the latest date for which the apple price was entered (04/11/12)
{MAX(([@[Fruit]]=”Apple”)*([Date]))}
As it’s an array formula, Ctl+Shift+Enter is required to get the curly braces.
Formula Names assume the data is formatted in a Excel 2010 table. It works fine with standard references too.
Further witchcraft can get the formula to return the latest price if required.
I think you are mixing your apples and bananas with your description of the last date for apples, bit of a fruit salad kicking in?
Right you are!
Although the health benefits of fruit salad are undisputed, the above should have read “The following formula will return the latest date for which the apple price was entered (03/10/12) …”
Use the function LASTROW() from Morefunc. It displays the last value in a column.
Hi
For me the formula =INDEX(A:A,MATCH(9E99,A:A)) gave me the last day in a series not the latest date in calendar
That is what Chandoo asked in the topic, to find the last day entered in the series, not the latest and that formula will provide the required solution.
Kyle
We can also use =LOOKUP(1000000000,B:B) to get the last value in entered in column B
Nice! I see now that we could use
=LOOKUP(9E99,B:B)
for latest number, and
=LOOKUP(“zzz”,B:B)
for latest text.
Cool tip!
Hi Jordan Goldmeier,
Thanks for the formula but when I tried it was giving an #Name error. However, when I tried {=INDIRECT(ADDRESS(MAX((A2:A22>1)*ROW(A2:A22)),1))} it worked.
Thanks
Remember that when you copy and paste from WordPress, the quotation marks come in as fancy, slanted-quotes, which Excel treats as characters and not quotes. If you replace the quotes in the Excel formula bar, then press CTRL+SHIFT+ENTER, it should work.
=OFFSET(“first date in colum”,SUM(COUNTA(“date range”),COUNTBLANK(“date range”),0))
***EDIT
Got a little too hasty typing my response.
If you know the range (A2:A100) this will give you the last entry. If you don’t know the last cell in the range…well you will need a more clever solution.
=OFFSET(A2, SUM(COUNTA(A2:A100), COUNTBLANK(A2:A100))-1,0)
Array enter
=MAX(MATCH({“?”,9.9E+307},A:A))
Will handle Mixed data types ( Text/Numbers/both )
The Question mark should be the Greek Letter Omega.
This is significantly faster than *
Interesting formula Sam.
Any specific reason for omega? I think even ~ should do (~ is ASCII Character 126)
There is an outside chance of having ~ in the data.
Omega has less chances. Infact to be very safe you can use double OmegaOmega
I looked at all this 9E99 stuff and could not get it to work, but knew what it was I really wanted to do.
I needed to find the row that had the last actual item in it from the column in question.
Then I had to return the data that was in that particular row.
If I used a helper column (b) on the first row, this would have contained the formula: =(A1<>”") giving me a column that was filled with true or false statements.
As these are numerical, I multiplied them in another column, by the row number that I was on, ie first row formula: =ROW(A1)*(A1<>”")
I now have a column of numbers that are either ’0′ or the ‘row number’.
I can use the ‘MAX’ command to extract the row with the last data.
Taking all this into an array formula gives my solution to the problem and actually dispenses with the extra columns of data by virtualising them within the array formula.
Hence: {=INDEX(A:A,MAX(ROW(A:A)*(A:A<>”")))}
Hi guys.
What if I say that I have a range o dates and I wanna know the OLDEST date by a given month?
I couldn’t answer it. Just could find the NEWEST with this formula:
{=MAX(–(MONTH(SHEET1!$D:$D)=5)*( SHEET1!$D:$D ))}
The MAX formula will answer it correctly because the Zeros given to the non-match cases won’t interfere, but in the MIN formula, they will, giving “01/00/00″ as response.
Can anybody give me a hand on it? I guess it’s a very nice challenge.
Sorry, the formula is
{=MAX(- -(MONTH(SHEET1!$D:$D)=5)*(SHEET1!$D:$D))}
Assuming:
1)my dates are in range A1:A100
2)no blank lines in between
3) dates not sorted
I use the following formula:
=INDEX(A1:A100,COUNTA(A1:A100))
And it gives me la last date in the range as recuested by Chandoo