Advance Filter between Dates

[GlennL]

Hello,

I am trying to get an advance filter to work with the criteria value range in 1 cell.

I have
=">="&WeekStart & "<="&WeekFinish

as the criteria filter range.

It works fine as
=">="&WeekStart
OR
"<="&WeekFinish
but not as a between range.

How do I get the between range to work when specifying it in one cell only?

 

[kevin9999]

Hello,

I am trying to get an advance filter to work with the criteria value range in 1 cell.

I have
=">="&WeekStart & "<="&WeekFinish

as the criteria filter range.

It works fine as
=">="&WeekStart
OR
"<="&WeekFinish
but not as a between range.

How do I get the between range to work when specifying it in one cell only?

Could you provide a sample of your data using the XL2BB add-in?

 

[James006]

Hi,

Take a look at the excellent explanation about Advanced Filter Criteria from Debra Dalgleigh

How to Set Up Excel Advanced Filter Criteria - Examples

Excel advanced filter examples take you beyond basic steps. See how to filter top records, blank cells, and more. Written steps, videos, Excel workbook

www.contextures.com

 

[Alex Blakenburg]

It would be worth your while to look at the Contextures site but see if this helps.
Note: With this method the Criteria range needs to have a heading and formula row (E1 & E2 in my example) BUT the heading row can be BLANK or any text that is NOT an exact match for any heading in your data range.

Book1
	A	B	C	D	E
1	Start	1/04/2023			Use Date Field
2	End	1/06/2023		Criteria --->	TRUE
3
4
5
6	ID	Date	Amt
7	1	1/01/2023	10
8	2	1/02/2023	20
9	3	1/03/2023	30
10	4	1/04/2023	40
11	5	1/05/2023	10
12	6	1/06/2023	20
13	7	1/07/2023	30
14	8	1/08/2023	40
15	9	1/09/2023	50
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AND(B7>=$B$7,B7<=$B$2)