*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).

**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.**

__Note:__
Hi Gonz,

ReplyDeleteHow about opportunities between 30 to 60 days? or 60 to 90 days? etc

Thanks

Hi Sharjeel,

DeleteI was wondering the same thing. Did you ever find a solution for that?

This is great post Gonzalo!

Thanks for this post , is there any way to use advance find , using a date field , where data greater then equal to date field.

ReplyDelete