Return latest date in range?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

I have a named range, with each row in the range having a repeating 8-
column data block (date, mode, three entered numbers, three calculated
numbers). Every time data is entered in one of these rows, it gets
tacked onto the end of whatever is in that row, and will always be the
same 8-column format. Not all rows have the same number of 8-column
entries.

Is there a formula I can enter in one cell at the top of the sheet
that will return the latest date in this entire range? Or would I
have to use a macro to search all dates in the range?

Ed
 
Say the dates are in column A and the dates are not sorted.

Because the dates are just numbers in a special format:
8 Spetember 2008 is just 39699
The latest date is the maximum value:

=MAX(A:A)
 
Thanks to all who responded, but I must apologize for not being clear
enough.

I have a range of about 150 rows down, and unknown columns to the
right. Each row is one item. When any one item is replaced, I enter
eight columns worth of data: date, mode, three numbers, three
calculated numbers.

This is done every time each item is replaced. So if Item 1 has been
replaced twice, it has 16 columns of data, with the first of each 8-
column block being a date - so there would be two dates. If Item 2
has been replaced five times, it has 40 columns of data, with five
dates.

I would like to know if there is an easy way to use a formula to
return the latest date from the entire 150-row, multi-column range.

Ed
 
Hi Ed,

=MAX(A1:A100,I1:I100,Q1:Q100) etc, depending on how many occurrences of blocks you can have per row

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a named range, with each row in the range having a repeating 8-
| column data block (date, mode, three entered numbers, three calculated
| numbers). Every time data is entered in one of these rows, it gets
| tacked onto the end of whatever is in that row, and will always be the
| same 8-column format. Not all rows have the same number of 8-column
| entries.
|
| Is there a formula I can enter in one cell at the top of the sheet
| that will return the latest date in this entire range? Or would I
| have to use a macro to search all dates in the range?
|
| Ed
 
Hi, Nick. That's what I was afraid of - I can't reference the whole
range, apparently; I'm going to have to break it down into 8-column
chunks. And I have to repeat this across a total of 10 worksheets
with identical formats. Looks like it's time for VBA!

Thanks to all for the input.
Ed

PS - Brain flash!!
This goes down to Row 160. So in B161 I put =MAX(B10:B160), select
all eight columns, and fill that across to put the MAX forumla at
every eighth column. Then in a cell above my headers I put =
MAX(B161:XEA161) (not that I think I'll need it out that far!).
Exactly what I needed!!
 
I have a named range, with each row in the range having a repeating 8-
column data block (date, mode, three entered numbers, three calculated
numbers). Every time data is entered in one of these rows, it gets
tacked onto the end of whatever is in that row, and will always be the
same 8-column format. Not all rows have the same number of 8-column
entries.

Is there a formula I can enter in one cell at the top of the sheet
that will return the latest date in this entire range? Or would I
have to use a macro to search all dates in the range?

Ed
Click to expand...

I think this will work, if I understand your setup correctly.

rng is your named range.
The formula assumes that, relative to the named range, the dates will only be
found in columns 1,9,16, etc.

=MAX(IF((COLUMN(Rng)-CELL("col",Rng))/8=INT(INT((COLUMN(Rng)-CELL("col",Rng))/8)),Rng))

This needs to be entered as an **array** formula. Hold down <ctrl><shift>
while hitting <enter> and Excel will place braces {...} around the formula if
you did it correctly.
--ron
 
Ron, it works great!! But it didn't need to be entered as an array
formula??!? Is this just a happy accident?

Would you be able to break this down a bit? It's pretty awesome, but
I'm having a hard time following what's going here/

Ed
 
Ron, it works great!!
Click to expand...

Glad to help. Thanks for the feedback.
But it didn't need to be entered as an array
formula??!? Is this just a happy accident?
Click to expand...

It is a happy accident based on the location of your table. As written the
table (Rng) could be anyplace on the sheet.

On my Excel 2007, it seemed to work OK with the table starting at A1. But if I
moved the table to, for example, D5, I got an incorrect answer without entering
it as an array formula.
Would you be able to break this down a bit? It's pretty awesome, but
I'm having a hard time following what's going here/
Click to expand...


=MAX(IF((COLUMN(Rng)-CELL("col",Rng))/8=INT(INT((COLUMN(Rng)-CELL("col",Rng))/8)),Rng))

Rng returns a 2D array of all the values in your table.
COLUMN(Rng) returns an array of the column numbers in the table

CELL("col",Rng) returns the column number of the first column in Rng

So subtracting one from the other "normalizes" the starting column number to 0.
Since the dates are in every 8th column, the only times the IF condition will
evaluate to TRUE will be when the normalized column number is 0,8,16, ...

INT(0/8) = 0/8
INT(8/8) = 8/8

But, of course INT(1/8) does not equal 1/8

If the condition is TRUE, then return the corresponding value in Rng which will
be a date. If the condition is FALSE, then return a FALSE.

So the IF statement returns an array which consists either of Dates, or of
FALSE's.

MAX ignores the Boolean, so will return the latest date in the array.

--ron
 
Glad to help. Thanks for the feedback.


It is a happy accident based on the location of your table. As written the
table (Rng) could be anyplace on the sheet.

On my Excel 2007, it seemed to work OK with the table starting at A1. But if I
moved the table to, for example, D5, I got an incorrect answer without entering
it as an array formula.



=MAX(IF((COLUMN(Rng)-CELL("col",Rng))/8=INT(INT((COLUMN(Rng)-CELL("col",Rng))/8)),Rng))

Rng returns a 2D array of all the values in your table.
COLUMN(Rng) returns an array of the column numbers in the table

CELL("col",Rng) returns the column number of the first column in Rng

So subtracting one from the other "normalizes" the starting column number to 0.
Since the dates are in every 8th column, the only times the IF condition will
evaluate to TRUE will be when the normalized column number is 0,8,16, ...

INT(0/8) = 0/8
INT(8/8) = 8/8

But, of course INT(1/8) does not equal 1/8

If the condition is TRUE, then return the corresponding value in Rng which will
be a date. If the condition is FALSE, then return a FALSE.

So the IF statement returns an array which consists either of Dates, or of
FALSE's.

MAX ignores the Boolean, so will return the latest date in the array.

--ron
Click to expand...

Just another point: The condition formula I have above could be re-written as:

=MOD(COLUMN(Rng)-CELL("col",Rng),8)=0.

So the formula could be shortened to:

=MAX(IF(MOD(COLUMN(Rng)-CELL("col",Rng),8)=0,Rng))

--ron
 
This is great!! Thanks ever so much!

Ed


Just another point:  The condition formula I have above could be re-written as:

=MOD(COLUMN(Rng)-CELL("col",Rng),8)=0.

So the formula could be shortened to:

=MAX(IF(MOD(COLUMN(Rng)-CELL("col",Rng),8)=0,Rng))

--ron- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top