How can i filter dates based on day of month
 

I want to filter out last 2 days of month from daily return data of a stock
for 20 years. Please suggest

Hi,

Assuming your dates are in column A, so A1 is a title, data starts on second
row.

1. If you meant that the dates must be the 2 last days of a month (Jan 30 & Jan
31 for example):

You leave D1 blank (this is important)
In D2: =DAY(A2+2)<3

Use the Advance Filter (Menu Data/Filter/Advanced Filter) and specify D1:D2 as
your CRITERIA range

2.
If you meant the last 2 days in the month amongst the data you have.
Add another column (called Is2LastDays), say column C
C1: Is2LastDays
in C2, the following array formula :
=A2=LARGE(IF(A$2:A$x-DAY(A$2:A$x)=A2-DAY(A2),A$2:A$x),2)

Copy C2 to Cx (as many rows as you have in column A).

Use the AutoFilter (Menu Data Filter/AutoFilter) and then pull down C1 to choose
the TRUE values.

Regards,

Daniel M.

"Saurabh" wrote in message
...
I want to filter out last 2 days of month from daily return data of a stock
for 20 years. Please suggest