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

Glad we got there in the end.

It would be more efficient if you were to do this in A1:

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

commit using CSE, and copy down to B30, then in B31 you can use this formula:

=MAX(IF(TEXT(A1:A60,"mmyyyy")=TEXT(A31,"mmyyyy"),A1:A60))

Use CSE to commit, then copy down to the bottom of your data. The second formula only looks at the previous 30 and the next 30 entries, rather than all 1500 of them.

Hope this helps.

Pete

P.S. Please mark the thread as Solved and click on the "star" icon if this has solved your query.