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.
44 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
how can you find the min?
@Matricia
If the range has blanks but no 0's use: =MIN(C1:C10)
If the range has 0's use: =MIN(IF(C1:C10>0,C1:C10)) Ctrl+Shift+Enter
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) ..."
Hi Adam,
I am trying to use this but not having success. Do you *have* to have your data in an Excel table? Or can you just have it in the spreadsheet like normal?
Using your example above, I am trying to have the raw data on one sheet (so your table above on one sheet) and then on a second sheet have a table where each fruit appears only once and the formula returns the last date that the price was changed for each fruit. So I would want the formula to reference the fruit name and then look for it on the other sheet and return the last date in the cell. Like a LOOKUP function... Does that make sense?
Thanks for your help.
stumped, do you think you can assist with the price formula?
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
how to get the latest and oldest date if I have date format like below:
2013-10-21 00:00:00
2013-11-27 00:00:00
2014-02-01 00:00:00
2014-02-21 00:00:00
2014-03-21 00:00:00
2014-06-02 00:00:00
Earliest date =min(range)
Latest date =max(range)
I have a similar question to Adam's above, but I'm not able to get the same results. Let's say my daughter wears a different shirt everyday and I want to know the last date she wore her green shirt:
Date Shirt
8/21/14 Green shirt
8/22/14 Blue shirt
8/23/14 Pink shirt
8/24/14 Red shirt
8/25/14 Green shirt
I'm trying this formula, but I'm getting the #REF! error:
=INDEX(MAX(A:A),MATCH("Green shirt",B:B,0))
I'm not at my computer to test but did you make it an array formula by hitting ctrl+shift+enter?
How do we get the highest value enter in one cell (not in a column)?
For e.g. particular cell say A1 captures the date. First time it is updated it would have values of 9/8/15. If again next it gets updated it will now have values of 9/8/15, 10/8/15 so on...
How can be the highest date captured?
Here's the format on Sheet1 of Excel
? suppose Columns are in A, B, C, D respectively.
S. No. Box Name Arrival Date No. of Pakgs
1 Box 1 19-Oct-15 4
2 Box 2 19-Oct-15 4
3 Box 3 20-Oct-15 5
4 Box 4 07-Nov-15 5
5 Box 5 08-Nov-15 4
I want to every package delivery date in below format, Let suppose in column F, G, H and I respectively.
Last Delivery Date 3rd Delivery 2nd Delivery 1st Delivery
The Master data is in Sheet2, having column box name, arrival date, delivery date, goods type etc
Please nelp me, how to get desire result.
HI,
I would like t find out nearest date from today(before and after today)
regards,
Ramya.