Date Functions
Click here to download a complete list of formulas.
TrackVia has built-in date functions. The table below lists the functions, the inputs required, and the calculated field types to employ when the functions are used alone. The functions can also be nested in formulas.
Place:
- Field names inside curly brackets
- Text in quotes
- Numbers without punctuation (quotes are optional).
You can reference a date using a date field {Date1} or statically, e.g. "2017-01-30" for January 30, 2017.
| Function | Description |
|---|---|
| now() | Returns the current date or date and time, depending on the Calculated field used. now() returns:
|
| today() | Returns the current date. today() returns:
|
| date() | Returns only the date from a date and time field. When {Start Date and Time} is "06/15/2017 04:29pm", date({Start Date and Time}) returns 06/15/2017. |
| year() month() day() weekday() |
Use these functions in a Calculated Text or Calculated Number field. year() returns the year for a date field. When {Date} is "2017-06-15", year({Date}) returns 2017. month() returns the number (1-12) of the month for a date field. When {Date} is "2017-06-15", month({Date}) returns 6. day() returns the number (1-31) of the day of the month for a date field. When {Date} is "2017-06-15", day({Date}) returns 15. weekday() returns the number (1-7) that corresponds to the day of the week for a date field. Sunday is assigned the value of 1. When {Date} is "2017-06-15", weekday({Date}) returns 5 (Thursday). (Gives the same result as dayofweek().) |
| dateadd() datesub() |
Adds or subtracts units of time from a date field. Use in a Calculated Date or Calculated DateTime field. The syntax is:
- {Date} is a Date or Date and Time field - n is the number of units to add or subtract - unit is one of the following (include the quotation marks in your formula):
|
| datedif() | Returns the number of months, days, hours, etc. between two date fields or two date and time fields. See dateadd() for the available units. Use in a Calculated Text or Calculated Number field. The syntax is: datedif({EndDate}, {StartDate}, "unit") When {StartDate} is June 1, 2017 and {EndDate} is June 15, 2017, datedif({EndDate}, {StartDate}, "d") returns 14. Note the end date is NOT included. |
| week() | Returns the week number for a date field. Weeks begin on Sunday. Use in a Calculated Text or Calculated Number field. When {Date} is June 15, 2017, week({Date}) returns 24. This function can also specify the start date or day for the 1st week of the year by adding a 2nd part called the "mode"; i.e. week(date, [mode]). The mode specifies whether a week begins on a Sunday or Monday and whether weeks should be numbered from 0-53 or 1-53. To view an article containing a detailed (and a bit confusing) explanation about mode, click here. |
| weekdays() | Returns the number of weekdays (Monday - Friday) spanned by 2 date fields and includes both dates in the count. Use in a Calculated Text or Calculated Number field. The syntax is weekdays({Date1},{Date2}). When {Date1} is June 1, 2017 and {Date2} is June 15, 2017, weekdays({Date1}, {Date2}) returns 11. |
| convert_tz() | Converts a date and time field from one time zone to another. For a list of available time zones, clickhere. Use in a Calculated Date Time field. The syntax is convert_tz({CurrentTimeZone}, {NewTimeZone}). When {Start Time} is used, convert_tz({Start Time}, "America/Chicago") returns the date and time for the Chicago time zone. |
| dayname() | Returns the day of the week (Sunday, Monday, etc.) for a date field. Use in a Calculated Text field. When {Date} is June 15, 2017, dayname({Date}) returns Thursday. |
| dayofweek() | Returns the number (1-7) that corresponds to the day of the week for a date field. Sunday is assigned the value of 1. Use in a Calculated Text or Calculated Number field. When {Date} is June 15, 2017, dayofweek({Date}) returns 5 (Thursday). Gives the same result as weekday(). |
| dayofyear() | Returns the day of the year (1-366) for a date field. Use in a Calculated Text or Calculated Number field. When {Date} is June 15, 2017, dayofyear({Date}) returns 166. |
| last_day() | Returns the last day of the month for a date field. Use in a Calculated Text or Calculated Date field. When {Date} is June 15, 2017, last_day({Date}) returns:
|
| hour() | Returns the number (0-23) that corresponds to the hour of the day for a date and time field. Use in a Calculated Text or Calculated Number field. When {Date and Time} is 06/15/2017 04:15pm, hour({Date and Time}) returns 16. |
| minute() | Returns the number (0-59) that corresponds to the minutes for a date and time field. Use in a Calculated Text or Calculated Number field. When {Date and Time} is 06/15/2017 04:15pm, minute({Date and Time}) returns 15. |
| monthname() | Returns the name of the month for a date field. Use in a Calculated Text field. When {Date} is June 15, 2017, monthname({Date}) returns June. |
| quarter() | Returns the number (1-4) that corresponds to the calendar quarter for a date field. Use in a Calculated Text or Calculated Number field. When {Date} is June 15, 2017, quarter({Date}) returns 2. |
Comments
0 comments
Article is closed for comments.