Excel.DateFilterCondition enum
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
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: { |
| afterOrEqualTo = "AfterOrEqualTo" | Date is after or equal to comparator date. Required Criteria: { |
| 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: { |
| beforeOrEqualTo = "BeforeOrEqualTo" | Date is before or equal to comparator date. Required Criteria: { |
| between = "Between" | Between Required Criteria: { |
| equals = "Equals" | Equals comparator criterion. Required Criteria: { |
| 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" |
|
| yearToDate = "YearToDate" | Date is in the same year to date. |
| yesterday = "Yesterday" | Date is yesterday. |