Find the latest date

[GerryK]

Could I please get a bit of help to find the latest date
entered somewhere in a column?
My situation comes from operator entered data from field
sheets so the last date entered in the column may not be
the most recent data date. I do not want to sort the sheet
based on that column.
Therefore, is it possible to find the date closest to
today from wherever it may be in the column. There may be
more than one entry but I do not care, it is just the
value nearest to today I'm after!

TIA

 

[Peo Sjoblom]

If they are numeric dates, look at the max function,

=MAX(Range)

will return the latest date, you might have to format the cell with the
formula
as date format

 

[Ron de Bruin]

You can use the Max function
(date is a number)

=MAX(B:B)

This will dispaly the latest date

 

[Bob Phillips]

Hi Gerry,

Here is one way

=MAX((G1:G100<TODAY())*(G1:G100))

it's an array formula, so commit with Ctrl-Shift-Enter


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Kent]

If you want the largest date that's not today when a list
includes today use =LARGE(A:A,2) which will find the 2nd
largest value or =LARGE(A:A,1)to find the largest in the
column. There's a SMALL(range,n) function that finds the
nth smallest too. Remember to format the cell for this
format as Date.

Excel Support Technician
www.canhelpyou.com
-----------------------------------------------------------

 

[GerryK]

Thank you all, that works fine.
It raised another question though.
I'd like a day(s)difference from today and the returned
date but I keep getting an #NUM error.

Here is my problem.
In my current sheet D5 I have =TODAY()
In I5 I have drawn from another sheet as suggested, assume:
=MAX(('FE'!D39999<TODAY())*('FE'!D39999)) array
entered.
and in F5 I have:
=IF(DATEDIF(D5,I5,"yd") & " day" & IF(DATEDIF(D5,I5,"yd")
<>1,"s"),"")
This is where I get the error message.

Any advice?

TIA

 

[Bob Phillips]

Gerry,

Is this what you need

=DATEDIF(I5,D5,"yd") & " day" & IF(DATEDIF(I5,D5,"yd")<>1,"s","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[GerryK]

Thanks Bob, it works now!

-----Original Message-----
Gerry,

Is this what you need

=DATEDIF(I5,D5,"yd") & " day" & IF(DATEDIF(I5,D5,"yd")

 

[Bob Phillips]

Wow! I wasn't quite sure what you were trying there.

Bob