Since views can be saved for frequent use, date fields have been updated to allow for date expressions or formulas. This means any field that has a date can also contain a formula for that date in the form of a date expression. The date expression allows you to search and save views without identifying a specific month, day, and year. Instead, the search is processed with a formula for the month, day, day of the week, and year based upon the relationship to the current date when the search is run.
To include the filter in a saved view for frequent use, follow our guide on how to manage views.
Note: Make sure system date and time are correct. When a date formula is entered, the system converts it to your intended time period allowing you to run searches and/or save them for future use without the need to modify dates.
Example:
Let's run a search for all trips with a start date in the current month.
Open the Trips folder.
Click + Add Filter and select Trip Start Date Range from the dropdown menu.
Select Expression from the Date/Expression dropdown menu.
Two new fields will appear for Start Expression and End Expression. Using the reference table below, we see that we need the codes for the 1st date of this month (=1,0,0) in the Start Expression field and the last date of this month (=31,0,0) in the End Expression field.
Click Apply Filter to view your results.
To save this as a view for future use, follow our manage views guide. Due to the dynamic nature of the codes used, your search results will always display information from the current month.
Date Expression Formulas Reference:
The following tables contain sample formulas for specifying dates. Mix and match to fit your needs. The first number specifies the day of the month, the second number specifies the month, and the third number specifies the year. So if you only have the first number filled in, this means you want this report to reoccur on this day every month, in every year.
|
If You Want This: |
Expression |
What It Shows |
When to Use It |
|
1stday of this month: |
=1,0,0 |
First Day of the current month |
|
|
Last day of this month: |
=31,0,0 |
Last day of the current month |
|
|
1stday of this year: |
=1,1,0 |
First day of the current year |
|
|
Last day of this year: |
=31,12,0 |
Last day of the current year |
|
|
Last Monday (will show today if today is Monday) |
=Mon |
Can use any day of the week |
|
|
A week from last Monday |
=Mon-1 |
Can use any day of the week |
|
|
Next Monday |
=Mon+1 |
Can use any day of the week |
|
|
Last week |
=Mon to =Sun-1 |
Last full week (Monday to Sunday) |
Weekly reconciliations or recent bookings |
|
One year from now |
=0,0,+1 |
|
|
| Last 12 Months | =1,-12,0 to =31,-1,0 | Full last 12 months (1st of month 12 months ago to end of last month) | Rolling year-over-year comparison, trends, or audits |
| Last 6 Months | =1,-6,0 to =31,-1,0 | Last 6 full months ending last month | Medium-term performance review |
| This Month | =1,0,0 to =31,0,0 | Entire current calendar month | Current activity tracking |
| Last Month | =1,-1,0 to =31,-1,0 | Full previous month | Monthly reconciliations or trend reports |
| Last 90 Days | =-90,0,0 to =0,0,0 | Last 90 days from today | Short-term trend monitoring |
| Year to Date (YTD) | =1,1,0 to = 0,0,0 | From January 1 of this year through today | Track year-to-date sales, trips, commissions |
| Next 30 Days | =+1,0,0 to =+30,0,0 | Tomorrow through 30 days out | Forecast upcoming departures or commissions |
| Trips Booked Last Week | |||
| Previous Fiscal Year (Jul - Jun) | =1,6,-2 to =30,5,-1 | Full Fiscal year from July 1 to June 30 | Compare current fiscal year to previous |
| Last Quarter | =1,-3,0 to =31,-1,0 | Last calendar quarter | Quarterly reconciliation |
| Next Month | =1,+1,0 to =31,+1,0 | All of next month | Upcoming business planning |
| Same Month Last Year (current Month) | =1,-12,0 to =31,-12,0 | Same month one year ago | Compare by season or month |
| Same Month Last Year (last Month) | =1,-1,-1 to =31,-1,-1 | Last full month one year ago | Compare by season or month |
| Last Year (Calendar) | =1,1,-1 to =31,12,-1 | January 1 to December 31 of last year | Year-over-year performance |
| Last 24 Months | =1,0,-2 to =31,-1,0 | From the 1st of the same month 2 years ago to end of last month | Year-over-year performance |
| Last 36 Months | =1,0,-3 to =31,-1,0 | From the 1st of the same month 3 years ago to end of last month | Year-over-year performance |
| Last 3 Years (Calendar) | =1,1,-3 to =31,12,-1 | Last 3 full calendar years, excluding current year | Historical analysis, special projects, or audits |