Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MONTH(date)-6, MON | Excel Worksheet Functions | |||
Month to date sales - reset in new month??? | Excel Worksheet Functions |