Latest Date in Any Month

Hi,

I have in column A a time series of consecutive daily dates covering around 5 years.

Can someone please suggest a formula that will return the latest date represented in column A for each month and year (e.g. 29 October for the month of October 2011 if column A contains the dates 1 to 29 October 2011) in the series.

Thanks very much!

How do you want this displayed? Do you have a separate list of month and year (in column B?) and you want the latest date in column C, or do you want the latest date to appear in column B against all dates?

Pete

Hi,

I'd prefer the latest date to appear in column B against all the dates.

Sorry I wan't clear earlier!

Okay, try this array* formula in B1:

=MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A$1:A$1500),A1))

Format as a date in the style you want to see, then copy down.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, instead of the usual <Enter>.

Hope this helps.

Pete

Thanks for the quick reply, Pete.

The formula doesn't seem to work - apparently there are too few arguments?

Thanks

Sorry, I missed a bit. Try this instead:

=MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A$1:A$1500,"mmyyyy"),A1))

Still need to commit it with CSE.

Hope this helps.

Pete

No, still not right - try this instead:

=MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A1,"mmyyyy"),A1))

That should do it.

Pete

Agh! I'm not thinking clearly this morning. This what it should be:

=MAX(IF(TEXT(A$1:A$1500,"mmyyyy")=TEXT(A1,"mmyyyy"),A$1:A$1500))

Use CSE to commit, then copy down.

Hope this helps. (Think I need a coffee)

Pete

Thanks works a treat, thanks very much!!!

Enjoy the coffee