Edit

Share via

Excel.DateFilterCondition enum

  • Reference
Package:
excel

Enum representing all accepted conditions by which a date filter can be applied. Used to configure the type of PivotFilter that is applied to the field.

Remarks

[ API set: ExcelApi 1.12 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-pivotfilters.yaml

await Excel.run(async (context) => {
  // Add a date-based PivotFilter.

  // Get the PivotTable.
  const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

  // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
  // If it's not already there, add "Date Updated" to the hierarchies.
  let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
  await context.sync();
  if (dateHierarchy.isNullObject) {
    dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
  }

  // Apply a date filter to filter out anything logged before August.
  const filterField = dateHierarchy.fields.getItem("Date Updated");
  const dateFilter = {
    condition: Excel.DateFilterCondition.afterOrEqualTo,
    comparator: {
      date: "2020-08-01",
      specificity: Excel.FilterDatetimeSpecificity.month
    }
  };
  filterField.applyFilter({ dateFilter: dateFilter });

  await context.sync();
});

Fields

after = "After"

Date is after comparator date.

Required Criteria: {comparator}. Optional Criteria: {wholeDays}.
afterOrEqualTo = "AfterOrEqualTo"

Date is after or equal to comparator date.

Required Criteria: {comparator}. Optional Criteria: {wholeDays}.
allDatesInPeriodApril = "AllDatesInPeriodApril"

Date is in April.
allDatesInPeriodAugust = "AllDatesInPeriodAugust"

Date is in August.
allDatesInPeriodDecember = "AllDatesInPeriodDecember"

Date is in December.
allDatesInPeriodFebruary = "AllDatesInPeriodFebruary"

Date is in February.
allDatesInPeriodJanuary = "AllDatesInPeriodJanuary"

Date is in January.
allDatesInPeriodJuly = "AllDatesInPeriodJuly"

Date is in July.
allDatesInPeriodJune = "AllDatesInPeriodJune"

Date is in June.
allDatesInPeriodMarch = "AllDatesInPeriodMarch"

Date is in March.
allDatesInPeriodMay = "AllDatesInPeriodMay"

Date is in May.
allDatesInPeriodNovember = "AllDatesInPeriodNovember"

Date is in November.
allDatesInPeriodOctober = "AllDatesInPeriodOctober"

Date is in October.
allDatesInPeriodQuarter1 = "AllDatesInPeriodQuarter1"

Date is in Quarter 1.
allDatesInPeriodQuarter2 = "AllDatesInPeriodQuarter2"

Date is in Quarter 2.
allDatesInPeriodQuarter3 = "AllDatesInPeriodQuarter3"

Date is in Quarter 3.
allDatesInPeriodQuarter4 = "AllDatesInPeriodQuarter4"

Date is in Quarter 4.
allDatesInPeriodSeptember = "AllDatesInPeriodSeptember"

Date is in September.
before = "Before"

Date is before comparator date.

Required Criteria: {comparator}. Optional Criteria: {wholeDays}.
beforeOrEqualTo = "BeforeOrEqualTo"

Date is before or equal to comparator date.

Required Criteria: {comparator}. Optional Criteria: {wholeDays}.
between = "Between"

Between lowerBound and upperBound dates.

Required Criteria: {lowerBound, upperBound}. Optional Criteria: {wholeDays, exclusive}.
equals = "Equals"

Equals comparator criterion.

Required Criteria: {comparator}. Optional Criteria: {wholeDays, exclusive}.
lastMonth = "LastMonth"

Date is last month.
lastQuarter = "LastQuarter"

Date is last quarter.
lastWeek = "LastWeek"

Date is last week.
lastYear = "LastYear"

Date is last year.
nextMonth = "NextMonth"

Date is next month.
nextQuarter = "NextQuarter"

Date is next quarter.
nextWeek = "NextWeek"

Date is next week.
nextYear = "NextYear"

Date is next year.
thisMonth = "ThisMonth"

Date is this month.
thisQuarter = "ThisQuarter"

Date is this quarter.
thisWeek = "ThisWeek"

Date is this week.
thisYear = "ThisYear"

Date is this year.
today = "Today"

Date is today.
tomorrow = "Tomorrow"

Date is tomorrow.
unknown = "Unknown"

DateFilterCondition is unknown or unsupported.
yearToDate = "YearToDate"

Date is in the same year to date.
yesterday = "Yesterday"

Date is yesterday.