Select Highest Date

MattB

Board Regular
Joined
Mar 4, 2002
Messages
63
I have 3 dates in 3 cells. Ie. 06/12/2001 cell H3, 24/11/01 in cell I3 and 06/01/01 in J3.
Is it possible to select the latest date of the 3 - I wish to show this in G3. Any ideas?
Many thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
On 2002-03-18 08:24, MattB wrote:
I have 3 dates in 3 cells. Ie. 06/12/2001 cell H3, 24/11/01 in cell I3 and 06/01/01 in J3.
Is it possible to select the latest date of the 3 - I wish to show this in G3. Any ideas?
Many thanks

you can use
=MAX(H3:J3)
 
Upvote 0
Many thanks - easy when you know how!
One more thing - is it possible to show which cell gives the return. If for instance I3 was the latest date - could the return in G3 also show this?
 
Upvote 0
Hard to do that without creating a circular reference. Easy to do in VBA. Try the following code to get the cell address:

=ADDRESS(3,MATCH(G3,H3:J3,0)+7,4)

The code assumes that the lookup range is H3:J3 and that the max is in cell G3.
 
Upvote 0
Building on Al's formula a bit, the following will show the latest date and the cell that it's in:-

=TEXT(MAX(H3:J3), "dd/mm/yyyy") &" "&ADDRESS(3,MATCH(MAX(H3:J3),H3:J3,0)+7,4)
 
Upvote 0
Replying on Al's behalf: -

3 = Row number
7 = offset of the column the formula is in from A (eg if your formula was in column B, this would equal 1).
4 = return relative cell reference.

Check out the help on the Address function for more info.
 
Upvote 0
Al / Mudface

Lastly!, I need to copy this thro' approx. 2000 rows, excuse my ignorance again but the ADDRESS (3 - obviously needs to match the row number, when i copy & paste this stays as 3 - how can I change this?

TEXT(MAX(H3:J3), "dd/mm/yyyy") &" "&ADDRESS(3,MATCH(MAX(H3:J3),H3:J3,0)+7,4)
 
Upvote 0
Ah. Do you have 3 different dates in each row or do you just want a reference returned to your dates in H3:K3?
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top