Looking for the latest date

  • Thread starter Thread starter Candee
  • Start date Start date
C

Candee

Hello and Happy Holidays to all!

I'm trying to find the best way (doesn't have to be the fastest) to
find the latest date in a range. My spreadsheet uses columns from A to
CB and rows from 1-1000. Each row contains a variety of data (Col A =
ID #, B= Tool Name, C= Date Calibrated, D=Date Due, E=Owner, F= Serial
#, J=Due Date, R=Due Date, approx. 20 different date columns, etc.)
I'm not sure if I can accomplish this with a formula or if it would be
best done in VB. Unfortuantely I cannot re-arrange the spreadsheet to
have all the dates in consecutive columns, I have to leave the set-up
of it the way it is.

Hopefully someone can help me out. Thanks in advance
 
Candee said:
Hello and Happy Holidays to all!

I'm trying to find the best way (doesn't have to be the fastest) to
find the latest date in a range. My spreadsheet uses columns from A to
CB and rows from 1-1000. Each row contains a variety of data (Col A =
ID #, B= Tool Name, C= Date Calibrated, D=Date Due, E=Owner, F= Serial
#, J=Due Date, R=Due Date, approx. 20 different date columns, etc.)
I'm not sure if I can accomplish this with a formula or if it would be
best done in VB. Unfortuantely I cannot re-arrange the spreadsheet to
have all the dates in consecutive columns, I have to leave the set-up
of it the way it is.

Hopefully someone can help me out. Thanks in advance
Click to expand...

Your date columns don't have to be consecutive. You can just use something
like this:
=MAX($C:$D,$J:$J,$R:$R)
or
=MAX($C1:$D1000,$J1:$J1000,$R1:$R1000)
 
Hi Candee

You can use the MAX function on multiple ranges of dates to get the
latest date. MAX does not require a single range but will accept
multiple ranges.

Example:
=MAX(A1:A17,C1:C18,E1:E8)
Format as a date

But make sure that the ranges only contain dates because otherwise you
could get an error. For example if the latest date was 10-Jan-2004 and
you put into one of the ranges a value of $38000 the MAX function will
pull out $38000 and convert it to a date (14-Jan-2004).




--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top