Last Date in Month

[Needhelp]

I have a list of 3,000 dates in a column, and I want to
delete every row in the column except the last day of each
month in the list. Unfortunately, the last day of the
month in the list may not be the exact end of the month.
In the example below, I want to delete every row except
the one containing 9/29/2003:

9/27/2003
9/28/2003
9/29/2003
10/2/2003
10/3/2003

I can't use EOMONTH to identify the row because the end of
September is 9/30, not 9/29. Any ideas?

 

[Daniel.M]

Hi,

Assuming your dates in A1:A3000,
put the following ARRAY formula in B1:
=IF(DAY(A1)=MAX((A1-DAY(A1)=A1:A3000-DAY(A1:A3000))* DAY(A1:A3000)),A1,"")

Copy B1 to B3000

Then copy/paste values to remove formulas and replace them with their
results.
Sort only column B to have all the rows with data (no "") appear at the top.

Have a nice day,

Daniel M.

 

[Tom Ogilvy]

Assume dates are sorted ascending with no duplicate dates
Assume yourdates are in column A, beginning in Row 2 (with a header in row
1)

in B2 put in the formula (or the next available column to the right of your
data)
=if(month(A2)<>Month(A3),"Keep","Delete")
then drag fill this down the column B to the end of your data

Select A1, do Data=>Filter=>Autofilter

in the dropdown in A1, select Delete

select all rows except row1 and do Edit=>Delete
(only visible rows will be deleted)

then do
Data=>filter=>Autofilter to turn off the filter

No select column B and do edit=>Clear

That should give you want you want