Question about MAX date

shdawson · Jan 4, 2011

Hi,

What function would be the best to use for evaluating a range of rows and pick the MAX date from a particular column? In other words, the newest date?

Have some data that has 3 columns:
project id
report date
status indicator.

...with LOTS of rows.

This is output from a repository where people input the status of a task. That status changes now and again. The output I have contains all of the rows (entries) into this repository, by project id.

Need to look at the project date, figure out the newest date, then grab the status id.

Thanks...

mikerickson · Jan 4, 2011

Have you tried MAX ?

Or are your dates really text strings?

shdawson · Jan 4, 2011

the date is like....12/24/2010

So, I guess it is the later.

?

S

shdawson · Jan 4, 2011

Thoughts on Formula:

Code:

=SUMPRODUCT(--('Status'!A$2:A$10000=C2),--MAX('Status'!A$2:A$10000),--GRAB('Status'!D$2:D$10000))

Not sure how to handle the MAX part.

GRAB is my psudeo-code to say...."get the value in the D column".

?

Thanks,
S

mikerickson · Jan 4, 2011

GRAB is not an Excel function, but it sounds like VLOOKUP is what you are looking for. Perhaps

=VLOOKUP(MAX(A:A), ,A1:G10000, 7, FALSE)

shdawson · Jan 4, 2011

Yeah, GRAB is pseudo-code.

Your example does not account for consiering the project id value. Need to:

1) match project id
2) get the max date
3) grab the value in the statu id column

?

shdawson · Jan 4, 2011

would this be a SUMPRODUCT thing?

jeffreybrown · Jan 5, 2011

SHD,

Can you post a small representative screen shot of your data with expected results?

http://www.excel-jeanie-html.de/index.php?f=1

shdawson · Jan 5, 2011

Jeffrey,

Hi. Thanks for the post.

This morning, I had to dig deeper and find an answer. So, a light came on to reference a previous posting. So, this posting has segued to...
http://www.mrexcel.com/forum/showthread.php?p=2563479#post2563479

Please let me know if this answers your questions of me now.

Thanks Bro,
S

Question about MAX date

shdawson

Active Member

mikerickson

MrExcel MVP

shdawson

Active Member

shdawson

Active Member

mikerickson

MrExcel MVP

shdawson

Active Member

shdawson

Active Member

jeffreybrown

Well-known Member

shdawson

Active Member

Similar threads

Forum statistics

Share this page

Question about MAX date

Active Member

MrExcel MVP

Active Member

Active Member

MrExcel MVP

Active Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock