DFILTER returns the Date that meets the condition specified by the SQL Query.

SYNTAX:

DFILTER(Offset, Grouping, GroupOffset, DateFormat, SQL,CellRef1..CellRef9)

Offset: Sets the offset of the filtered (selected) values for which the date should be returned.  For example, to get the date of the second  value in the selected list, pass 2.

Grouping: Used to set which dates to calculate the stat for. Common Groupings are:

"D" Days
“M” Monthly 
"R" Report Dates
Click here for available Grouping options

GroupOffset: Used with the Grouping parameter to set the dates to calculate the stat for.  Sets the number of Groups (set by the Grouping Parameter) to be added to set the dates.  

DateFormat:  The format that the date will be printed in. See here for format explanation.

SQL: The SQL Select statement to filter the records with.  The DFILTER SQL statement supports referring to the value in cells with the @CELL_xx@ and the @CELL~xx@ keywords.  It also supports referring to the grouping date range specified by GroupOffset and Grouping through the gSD and gED keywords:

gSD:  Will be replaced by the start date of the grouping as specified by the GroupOffset and Grouping parameters.

gED:  Will be replaced by the end date of the grouping as specified by the GroupOffset and Grouping parameters.

@CELL_xx@: To refer to a cell that contains a value.  For example, to setup an SQL statement that checks whether a Variable is > the value in Cell F2 your SQL Statement would be:

select distinct datestamp from DATATBL where (varid = 1 and curvalue > @CELL_F2@) AND (DateStamp >= gSD and DateStamp < gED)

The @CELL_F2@ will be replaced with the value that is in cell F2, therefore if F2 contains 12.5 the actual query executed would be:

select distinct datestamp from DATATBL where (varid = 1 and curvalue > 12.5) AND (DateStamp >= '2008-01-01' and DateStamp < '2008-01-31')

@CELL~xx@: To refer to a cell that contains a varnum.  For example, to setup an SQL statement that checks whether a Variable as specified in Cell F2 is greater than 4 your SQL Statement would be:

select distinct datestamp from DATATBL where (varid = @CELL~F2@ and curvalue > 4) AND (DateStamp >= gSD and DateStamp < gED)

The @CELL~F2@ will be replaced with the varid for the variable number that is in cell F2, therefore if F2 contains 4011 and the Varid for variable 4011 is 47 the actual query executed would be:

select distinct datestamp from DATATBL where (varid = 47 and curvalue > 4) AND (DateStamp >= '2008-01-01' and DateStamp < '2008-01-31')

CellRef1..CellRef9: For all cells that are referred to using the @CELL keyword, the cell must be listed in the cell reference parameters.  This tells the DFILTER formula to recalculate if any value changes in those cells. 

NOTES:

Use Locate, Filtered Dates to place this formula. Enter First Condition and Second Condition using [and/or] function and enter operator and value to filter data.

EXAMPLES:

Show Days where the Flow is greater than 4. 

DFILTER(1,"R",1,"mm/dd/yyyy","select distinct datestamp from DATATBL where (varid = 1 and curvalue > 4) AND (DateStamp >= gSD and DateStamp < gED)")  returns the 1st date in the report date range where Varid is 1 (Influent Flow variable) and the value is greater than 4.

DFILTER(1,"R",1,"mm/dd/yyyy","select distinct datestamp from DATATBL where (varid = @CELL~F2@ and curvalue > @CELL_B2@) AND (DateStamp >= gSD and DateStamp < gED)",F2,B2) returns the first date in the report date range where the variable as specified in cell F2 is greater than the value in cell B2.