ExcelScript.PivotDateFilter interface

Package:

ExcelScript

Configurable template for a date filter to apply to a PivotField. The condition defines what criteria need to be set in order for the filter to operate.

Properties

comparator	The comparator is the static value to which other values are compared. The type of comparison is defined by the condition.
condition	Specifies the condition for the filter, which defines the necessary filtering criteria.
exclusive	If true, filter excludes items that meet criteria. The default is false (filter to include items that meet criteria).
lowerBound	The lower-bound of the range for the between filter condition.
upperBound	The upper-bound of the range for the between filter condition.
wholeDays	For equals, before, after, and between filter conditions, indicates if comparisons should be made as whole days.

Property Details

comparator

The comparator is the static value to which other values are compared. The type of comparison is defined by the condition.

comparator?: FilterDatetime;

Property Value

ExcelScript.FilterDatetime

condition

Specifies the condition for the filter, which defines the necessary filtering criteria.

condition: DateFilterCondition;

Property Value

ExcelScript.DateFilterCondition

Examples

/**
 * This script applies a filter to a PivotTable that filters out rows 
 * that aren't from this month.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the "Date Recorded" field to filter. 
  // The data in this field must be dates in order for the filter to work.
  const pivot = workbook.getPivotTables()[0];
  const rowHierarchy = pivot.getRowHierarchy("Date Recorded");
  const rowField = rowHierarchy.getFields()[0];

  // Apply the date filter.
  rowField.applyFilter({
    dateFilter: {
      // Setting the condition to `thisMonth` means items that are before or
      // after this month will not be displayed.
      condition: ExcelScript.DateFilterCondition.thisMonth
    }
  });
}

exclusive

If true, filter excludes items that meet criteria. The default is false (filter to include items that meet criteria).

exclusive?: boolean;

Property Value

boolean

lowerBound

The lower-bound of the range for the between filter condition.

lowerBound?: FilterDatetime;

Property Value

ExcelScript.FilterDatetime

Examples

/**
 * This script applies a filter to a PivotTable that filters it
 * to only show rows from between June 20th, 2022 and July 10th, 2022.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the "Date Recorded" field to filter. 
  // The data in this field must be dates in order for the filter to work.
  const pivot = workbook.getPivotTables()[0];
  const rowHierarchy = pivot.getRowHierarchy("Date Recorded");
  const rowField = rowHierarchy.getFields()[0];

  // Create the filter's date boundaries.
  let earliestDate: ExcelScript.FilterDatetime = {
    date: "2022-06-20",
    specificity: ExcelScript.FilterDatetimeSpecificity.day
  };
  let latestDate: ExcelScript.FilterDatetime = {
    date: "2022-07-10",
    specificity: ExcelScript.FilterDatetimeSpecificity.day
  };

  // Apply the date filter.
  rowField.applyFilter({
    dateFilter: {
      condition: ExcelScript.DateFilterCondition.between,
      lowerBound: earliestDate,
      upperBound: latestDate
    }
  });
}

upperBound

The upper-bound of the range for the between filter condition.

upperBound?: FilterDatetime;

Property Value

ExcelScript.FilterDatetime

wholeDays

For equals, before, after, and between filter conditions, indicates if comparisons should be made as whole days.

wholeDays?: boolean;

Property Value

boolean