Find the latest date

  • Thread starter Thread starter GerryK
  • Start date Start date
G

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
 
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
 
You can use the Max function
(date is a number)

=MAX(B:B)

This will dispaly the latest date
 
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)
 
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
-----------------------------------------------------------
 
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'!D3:D9999<TODAY())*('FE'!D3:D9999)) 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
 
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)
 
Back
Top