advanced filter - criteria range

[jim may]

Below is the Critera Range of my Advanced Filter project;
The User is Inputting into Cells B2 and C2 with Dates

The Actual Criteria Range is B4:F5;

I have formulas in B5 and C5 which reference B2 and C2; Currently, as you see
I've "attempted" to allow for if Cells B2 and C2 are BLANKED-OUT by the User
meaning "regardless of dates" -- But it is not working.. Any suggestions?

TIA,
Jim

Excel Workbook
	B	C	D	E	F
1	Start	End
2	5-Aug	7-Aug
3
4	TransDate	TransDate	DeptName	Item#	ItemDesc
5	>=40760	<=40762			Pepper
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B5	=IF(ISBLANK(B2),"",">=" &B2)
C5	=IF(ISBLANK(C2),"","<=" &C2)

 

[shg]

How about instead,

=">=" & N(B2)

="<=" & IF(C2, C2, --"12/31/9999")

 

[tweedle]

Try...



<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B5</TD><TD>=IF(ISBLANK(B2),">="&1,">=" &B2)</TD></TR><TR><TD>C5</TD><TD>=IF(ISBLANK(C2),"<="& TODAY() * 2,"<=" &C2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

or derivative

 

[jim may]

This seems to work...

Excel Workbook
	B	C
4	TransDate	TransDate
5	>=40760	<=40762
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B5	=IF(ISBLANK(B2),">" & DATEVALUE("1/1/1900"),">=" &B2)
C5	=IF(ISBLANK(C2),"<" & DATEVALUE("8/1/2500"),"<=" &C2)