Last Date in Month
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? |
Last Date in Month
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. "Needhelp" wrote in message ... 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? |
Last Date in Month
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 -- Regards, Tom Ogilvy Needhelp wrote in message ... 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? |
All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com