S
Saurabh
I want to filter out last 2 days of month from daily return data of a stock
for 20 years. Please suggest
for 20 years. Please suggest
D
Daniel.M
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
2 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.
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
2 asyour 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.