Filtering daily data to monthly data using Filter or alternative method

Hi all,

Please kindly see attached excel spreadsheet.

I have a sample of data that I need to test, the raw data is daily, and I'm trying to filter to show ONLY the value at the end of each month.
How should I achieve this using the filter function?

Thank you for your help.sample.xls

also im trying to find the end of month to month change of the values in column. if i were to successfully use the filter function to find the end of each month, would i be able to use the =(Month/month-1) and drag it down?

Hi Jonnyyyl,

You can change the date format to just "dd" and they you can easily filter out the data for 30 or 31, see the attached file for better explanation on this. Thanks.

regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome jonnyyyl..... and thanks for the kiss

the last day of a month can be 31,30,29 and since these are three i.e., more than two, you might need to use Advanced Filter instead of Auto Filter. Thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

If your data sorted in ascending order,

Insert 3 rows above the heading, so Company A should be in A4, then in A2 enter,

=A6=LOOKUP(EOMONTH(A6,0),$A$6:$A$2968)

Select $A$5:$B$2968, go to Data >> Advanced Filter

List Range: $A$5:$B$2968
Criteria: $A$1:$A$2

click OK

Will look the last entry in the month.

eg: in January 2000, If the last entry is 1/28/2000 will filter this date

See the attached.