Date Functions

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:
  • 2017-06-15 16:29:32 in a Calculated Text field
  • 06/15/2017 in a Calculated Date field
  • 06/15/2017 04:29pm in a Calculated Date Time field.
today() Returns the current date.

today() returns:
  • 2017-06-15 in a Calculated Text field
  • 06/15/2017 in a Calculated Date field
  • 06/15/2017 12:00am in a Calculated Date Time field.
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:
  • dateadd({Date}, n, "unit")
  • datesub({Date}, n, "unit")
where
- {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):
  • "yyyy": Year
  • "q": Quarter
  • "m": Month
  • "d": Day
  • "ww": Week
  • "h": Hour
  • "n": Minute
  • "s": Second.
When the date is June 15, 2017, dateadd("2017-06-15",2,"yyyy") returns:
  • 06/15/2019 in a Calculated Date field
  • 06/15/2019 12:00am in a Calculated Date Time field.
When {Date} is June 15, 2017, datesub({Date}, 2,"m") returns:
  • 04/15/2017 a Calculated Date field
  • 04/15/2017 12:00am in a Calculated Date Time field.
Use Case example: If you generally ship two days after an order date, you would use this formula in a Calculated Date field for Shipping Date:  dateadd({Order Date}, 2, "d").
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, click here.
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:
  • 2017-06-30 in a Calculated Text field
  • 06/30/2017 in a Calculated Date field.
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.
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.

You can use the date a record is created or updated in a formula by referencing the field name in all uppercase letters.  For example, the CREATED field indicates the date and time a record was added to a table and using

dateadd({CREATED}, 1, "m")

will add a month to the Created field.

Feedback and Knowledge Base