Select Highest Date

MattB · Mar 18th, 2002, 09:24 AM

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

IML · Mar 18th, 2002, 09:26 AM

Quote:

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)

MattB · Mar 18th, 2002, 02:54 PM

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?

Al Chara · Mar 18th, 2002, 03:40 PM

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.

Mudface · Mar 18th, 2002, 03:51 PM

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)

Al Chara · Mar 18th, 2002, 03:53 PM

Good thought Mudface, I didn't even think of that.

MattB · Mar 18th, 2002, 04:09 PM

Sorry Al, what do the 3, 7 & 4 denote?

Mudface · Mar 18th, 2002, 04:27 PM

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.

MattB · Mar 18th, 2002, 05:01 PM

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)

Mudface · Mar 18th, 2002, 05:48 PM

Ah. Do you have 3 different dates in each row or do you just want a reference returned to your dates in H3:K3?

Mar 18th, 2002, 03:40 PM	#4
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	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. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 09:24 AM	#1
MattB Board Regular Join Date: Mar 2002 Posts: 62	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

Mar 18th, 2002, 02:54 PM	#3
MattB Board Regular Join Date: Mar 2002 Posts: 62	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?

Mar 18th, 2002, 03:51 PM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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)

Mar 18th, 2002, 03:53 PM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Good thought Mudface, I didn't even think of that.

Mar 18th, 2002, 04:09 PM	#7
MattB Board Regular Join Date: Mar 2002 Posts: 62	Sorry Al, what do the 3, 7 & 4 denote?

Mar 18th, 2002, 04:27 PM	#8
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Mar 18th, 2002, 05:01 PM	#9
MattB Board Regular Join Date: Mar 2002 Posts: 62	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)

Mar 18th, 2002, 05:48 PM	#10
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Ah. Do you have 3 different dates in each row or do you just want a reference returned to your dates in H3:K3?