Finding the most recent/latest date.......

[Natway03]

Hi,

I have a list of dates in column A and a list of names in column B. I would like to match each name from column B to the most recent / latest date from column A. Any help would be immensely appreciated.

Many thanks,
Natway

[jason.b75]

This formula must be Array confirmed with Shift Ctrl Enter.

=MAX(IF(B$2:B$100=B2,A$2:A$100))

Adjust the ranges to suit your real data range.

[Natway03]

Hi Jason,

Thanks for coming back to me, however I am still not getting the latest date for some reason.

cabnumber Status
W859WGT 06/12/2012

The above CAB numbers last date of upgrade was 07/31/2012 (the date and time are US format) and I get 06/12/2012. I am not sure if the formula actually brings me the most recent date. By the way the first date of upgrade this year was 3/22/2012 and the last date was 07/31/2012 if this helps. Thanks again for assisting!

[jason.b75]

Non - local date formats are always a pain to work with, depending on how they're formatted, it's highly likely that excel is not recognising them as 'proper' dates, which would explain the incorrect results.

The easiest way to deal with it would be to convert the dates to UK format, either by overwriting the original dates, or by adding a new column.

Is either of these permissable before I start looking for alterantives?

[Natway03]

I agree with your comment and think that this is the case. I will reformat and try the formula again. The source data has the date format mixed up as in some places it reads 07/12/2012 and in some places it reads 7/12/2012.

Hopefully the formula should work once the formatting is changed.

[jason.b75]

Quick re-format tip.

Select your date column, then on the excel ribbon / menu navigate to

Data > Text to columns. Click 'Next' twice. Set the 'Date' Dropdown to MDY (change the Destination range if you want the reformatted dates in a different column) then click Finish.

[Natway03]

Hi Jason,

Hope you had a good weekend. You are a Genius and thanks for assisting with my query- my report was brilliant!

Thanks again and pls stay in touch.

natway