Submit a request

How can we help?

Articles in this section

See more

Relative & annual date values

Updated

Filtering on a date field does not require a specific date. A relative value such as this week, last month, 3 months ago, or last quarter resolves each time the filter runs, which is what makes a saved report or list view stay current.

Click the information icon in the date value field to see the available options.

Relative Date Filter Options in List Filters — The image shows the List Filters panel in Insightly CRM with a “Contact Created” filter set to “This Fiscal Year,” and a tooltip displaying all supported relative date values such as Yesterday, Last Week, Last Month, This Quarter, and Last Fiscal Year. This is useful for readers learning how to filter contact lists using dynamic date ranges without entering specific dates.

On this page:

Where relative dates work

  • Dashboard Cards: Date Filter
  • Dynamic List Rules
  • Finish Actions: Create List
  • Form Handlers: Add To List Action
  • Journey Checks: Prospect Steps
  • Reports
  • Service Automation
  • Standard List View Filters
  • Workflow Criteria Filters

Annual dates

Typing annually at the end of a relative date field runs the filter while disregarding the year. Annual dates cannot span more than one year. The keyword is case insensitive.

The standard use is a birthday list. Build a contacts list view or report with filter criteria of Date of Birth equal to Next Month Annually, and Insightly returns every contact whose birth month is next month, regardless of birth year.

Birthday Filter Results in Contact List — A CRM contact list filtered by “Date Of Birth equal to Next Month annually,” displaying four contacts with their names, birth dates, and creation dates. This filter helps users identify contacts with upcoming birthdays in the following month for outreach or marketing purposes.
List Filter for Annual Birthday Date Matching — This screenshot shows a list filter configuration in Insightly CRM where contacts are filtered by “Date Of Birth” set to “equal to” with a value of “Next Month Annually.” This filter would be useful for identifying contacts whose birthdays fall in the upcoming month on a recurring yearly basis.

Relative date values

Value Range
YESTERDAY Starts at 12:00:00 a.m. on the day before the current day and continues for 24 hours.
TODAY Starts at 12:00:00 a.m. on the current day and continues for 24 hours.
TOMORROW Starts at 12:00:00 a.m. on the day after the current day and continues for 24 hours.
LAST WEEK Starts at 12:00:00 a.m. on the first day of the week before the current week and continues for seven days.
THIS WEEK Starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.
NEXT WEEK Starts at 12:00:00 a.m. on the first day of the week after the current week and continues for seven days.
LAST n WEEKS Starts at 12:00:00 a.m. on the first day of the week that started n weeks before the current week, and continues up to 11:59 p.m on the last day of the week before the current week.
NEXT n WEEKS Starts at 12:00:00 a.m. on the first day of the week after the current week and continues for n times seven days.
n WEEKS AGO Starts at 12:00:00 a.m. on the first day of the week that started n weeks before the start of the current week and continues for seven days.
LAST MONTH Starts at 12:00:00 a.m. on the first day of the month before the current month and continues for all the days of that month.
THIS MONTH Starts at 12:00:00 a.m. on the first day of the current month and continues for all the days of that month.
NEXT n MONTHS Starts at 12:00:00 a.m. on the first day of the month after the current month and continues until the end of the nth month.
LAST n MONTHS Starts at 12:00:00 a.m. on the first day of the month that started n months before the current month and continues up to 11:59 p.m on the last day of the month before the current month.
n MONTHS AGO Starts at 12:00:00 a.m. on the first day of the month that started n months before the start of the current month and continues for all the days of that month.
NEXT MONTH Starts at 12:00:00 a.m. on the first day of the month after the current month and continues for all the days of that month.
LAST 90 DAYS Starts at 12:00:00 a.m. 90 days before the current day and continues up to the current second. (The range includes today.)
NEXT 90 DAYS Starts at 12:00:00 a.m. on the day after the current day and continues for 90 days. (The range does not include today.)
LAST n DAYS Starts at 12:00:00 a.m. n days before the current day and continues up to the current second. (The range includes today. Using this date value creates a list including records from n + 1 days ago up to the current day.)
NEXT n DAYS Starts at 12:00:00 a.m. on the next day and continues for the next n days. (The range does not include today.)
n DAYS AGO Starts at 12:00:00 a.m. on the day n days before the current day and continues for 24 hours. (The range does not include today.)
LAST QUARTER Starts at 12:00:00 a.m. on the first day of the calendar quarter before the current calendar quarter and continues to the end of that quarter. The quarter is determined by the fiscal year that is set through company settings .
THIS QUARTER Starts at 12:00:00 a.m. on the first day of the current calendar quarter and continues to the end of the quarter. The quarter is determined by the fiscal year that is set through company settings .
NEXT QUARTER Starts at 12:00:00 a.m. on the first day of the calendar quarter after the current calendar quarter and continues to the end of that quarter. The quarter is determined by the fiscal year that is set through company settings .
LAST n QUARTERS Starts at 12:00:00 a.m. on the first day of the calendar quarter n quarters ago and continues to the end of the calendar quarter before the current quarter. (The range does not include the current quarter.) The quarter is determined by the fiscal year that is set through company settings .
NEXT n QUARTERS Starts at 12:00:00 a.m. on the first day of the calendar quarter after the current quarter and continues to the end of the calendar quarter n quarters in the future. (The range does not include the current quarter.) The quarter is determined by the fiscal year that is set through company settings .
n QUARTERS AGO Starts at 12:00:00 a.m. on the first day of the calendar quarter n quarters before the current calendar quarter and continues to the end of that quarter. The quarter is determined by the fiscal year that is set through company settings .
LAST YEAR Starts at 12:00:00 a.m. on January 1 of the year before the current year and continues through the end of December 31 of that year.
THIS YEAR Starts at 12:00:00 a.m. on January 1 of the current year and continues through the end of December 31 of the current year.
NEXT YEAR Starts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of that year.
n YEARS AGO Starts at 12:00:00 a.m. on January 1 of the calendar year n years before the current calendar year and continues through the end of December 31 of that year.
LAST n YEARS Starts at 12:00:00 am on January 1, n+1 years ago. The range ends on December 31 of the year before the current year.
NEXT n YEARS Starts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of the nth year.
LAST FISCAL YEAR Starts at 12:00:00 a.m. on the first day of the fiscal year BEFORE the current year (determined by the fiscal year that is set through company settings ) and continues to the end of that fiscal year.
NEXT FISCAL YEAR Starts at 12:00:00 a.m. on the first day of the fiscal year AFTER the current year (determined by the fiscal year that is set through company settings ) and continues to the end of that fiscal year.
n FISCAL YEARS AGO Starts at 12:00:00 a.m. on the first day of the fiscal year BEFORE the current year, n years ago (determined by the fiscal year that is set through company settings ) and continues to the end of that fiscal year before the current year.
Next n FISCAL YEARS Starts at 12:00:00 a.m. on the first day of the fiscal year AFTER the current year, n+1 years ahead (determined by the fiscal year that is set through company settings ) and continues to the end of that fiscal year.

ℹ️ Quarter and fiscal year values depend on the fiscal year set in company settings, not the calendar year.

Related to

Related articles

Powered by Zendesk