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 |
Enter This |
|
1stday of this month: |
=1,0,0 |
|
Last day of this month: |
=31,0,0 |
|
1stday of this year: |
=1,1,0 |
|
Last day of this year: |
=31,12,0 |
|
Seven days from now: |
=+7,0,0 |
|
Last Monday (will show today if today is Monday) |
=Mon |
|
A week from last Monday |
=Mon-1 |
|
Next Monday |
=Mon+1 |
|
Three months from now |
=0,+3,0 |
|
Six months ago: |
=0,-6,0 |
|
One year from now |
=0,0,+1 |
|
Year to Date |
=1,1,0 to = |
|
Last 12 months |
=0,-12,0 to =31,-1,0 |