Date and Time operators in CRM Explained

When you add conditions to Advanced Find in CRM to filter records based on a DateTime field, some operators such as “Today” are quite clear, while others such as “Next X Weeks” are trickier (e.g. When does the week start?). This post explains in detail what these operators mean and how to combine them to create more powerful filters.

Day Operators

Next X Days: Starts now and includes the next X days (regardless of time). E.g. If it is 3:30PM, “Next 1 Days” includes today after 3:30 PM and goes until the end of the day tomorrow (11:59PM). Note that this is not the same as “Next 24 hours”. When the field is only Date (no Time particle) then usually the time is set to 12AM (hidden) so this operator will not include today.

Last X Days: Includes all values that are before now and on/after X days ago. E.g. If it is 3:30PM, “Last 1 Days” includes today (before 3:30 PM) as well as yesterday the entire day (12AM-11:59PM). Note that this is not the same as “Last 24 hours”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next 7 Days: Behaves the same as “Next X Days” when X == 7.

Hour Operators

Next X Hours: Behaves like “Next 60*X Minutes”. It starts now and it includes X hours ahead. E.g. If it is 3:30PM then “Next 1 Hours” includes from 3:30PM – 4:30PM.

Last X Hours: Includes all values in the current hour before now, as well as all values in the past X hours. (NOT the same as “Last 60*X Minutes). E.g. If it is 3:30PM then “Last 1 Hours” includes from 2:00PM to 3:29PM).

Week Operators

Next Week: This is NOT the same as “Next X Weeks” where X == 1. It includes all values starting on the first day of next week and includes the 6 days after that one. The first day of next week is configurable in the System Settings section. For example, the week can start next Sunday (Default for U.S.) or next Monday (Default for U.K.).



This operator uses a “calendar week”. As an example, if it is Friday and the first day of the week is Sunday then “Next Week” means all values from next Sunday until next Saturday.

Last Week: This is NOT the same as “Last X Weeks” where X == 1. This operator includes all values before the current calendar week. The calendar week depends on the “Frist Day of Week” system setting. For example, if today is Saturday and the “First Day of Week” is Sunday then “Last Week” would include all values starting 2 Sundays ago and until last Saturday.

This Week: Includes all values of the current calendar week.

Next X Weeks: Same behaviour as “Next X*7 Days”. (Not the same as “Next Week” when X == 1). E.g. “Next 2 Weeks” is the same as “Next 14 Days”.

Last X Weeks: Same behaviour as “Last X*7 Days”. (Not the same as “Last Week” when X == 1). E.g. “Last 2 Weeks” is the same as “Last 14 Days”.

Month Operators

Next Month: Includes all values of the next calendar month. If we’re in August then it includes all values in September (same year).

Last Month: Includes all values of the last calendar month (before the current month). If we’re in August then it includes all values in July (same year).

This Month: Includes all values of the current calendar month. If we’re in August then it includes all values in August (same year).

Last X Months: This operator does not use the calendar year. It includes all values before now and on/after the same calendar day on the previous month. E.g. if it is 3:30PM on August 3rd then “Last 1 Months” includes all values between July 3rd all day (since 12AM) and today at 3:29PM. When X == 1, this is NOT the same as “Last Month”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next X Months: This operator does not use the calendar year. It includes all values starting now and until the same calendar day on the next month (inclusive). E.g. if it is 3:30PM on August 3rd then “Next 1 Months” includes all values between now (3:30PM) and September 3rd (until 11:59PM). When X == 1, this is NOT the same as “Next Month”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would not include today in that case.

Older than X Months: This operator does not use the calendar year. It includes all values before the current calendar day of the previous month. E.g. if it is 3:30PM on August 3rd then “Older than 1 Months” operator would include all values on or before July 2nd at 11:59PM.

Year Operators

Next Year: Includes all values of the next calendar year.

Last Year: Includes all values of the last calendar year.

This Year: Includes all values of the current calendar year.

Last X Years: This operator does not use the calendar year. It includes all values before now and on/after the same calendar day on the previous year. E.g. if it is 3:30PM on August 3rd 2012 then “Last 1 Years” includes all values between August 3rd 2011 all day (since 12AM) and today at 3:29PM. When X == 1, this is NOT the same as “Last Year”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next X Years: This operator does not use the calendar year. It includes all values starting now and until the same calendar day on the next year (inclusive). E.g. if it is 3:30PM on August 3rd 2012 then “Next 1 Year” includes all values between now (3:30PM) and August 3rd 2013 (until 11:59PM). When X == 1, this is NOT the same as “Next Year”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would not include today in that case.

Other Operators

Any Time: Same as “Contains Data”. It will return all values which are not blank.

Composite (logical) Operators

Given the operators in Advanced Find, you can combine them and group them to achieve more complex operators. These are some typical examples:

Older Than X Years: Simply multiple X by 12. E.g. “Older than 2 years” is the same as “Older than 24 Months” which is a supported operator.

On Year 2008: This is equivalent to combining “On Or After” 2008-01-01 and “On Or Before” 2008-12-31. You can use the same logic for filters such as “In January 2010”

In the Past: You can combine “Last 1 Months” and “Older than 1 Months” to include all the values in the past.

Next 3 Calendar Weeks/Months: Unfortunately you cannot build such a query from DateTime fields. You would have to either store the week/month in a separate numeric field or otherwise build a report that applies additional Datetime expressions to filter the results. CRM only supports filtering by calendar week/month for last, this and next week/month. (same applies to year).



Note: Most of these operators depend on the user’s time zone. For example, a DateTime field value can be “Today” for users in Italy, but “Tomorrow” for users in Canada.