How-To Use Date Formulas

Foxtrot is capable of utilizing many different types of formulas to change, modify, or structure data depending on your needs. To find additional information, read these articles that show how to use most commonly used formulas in Foxtrot:

• How-To Use LeftOf & RightOf Formulas
• How-To Use Remove & Replace Formulas
• How-To Use Text Formulas

In this article, we will cover some of the essential date formulas in Foxtrot that will enable you to work with various different date values. Note that it is important to make sure that any input to formulas in Foxtrot must be formatted with American format, otherwise, the formulas will not work properly. For more information on how to work with different formats in Foxtrot, please see this article:

• How-To Use Culture Support

DateAdd

The DateAdd formula is one of the most common date formulas used in Foxtrot. It returns a date to which a specified time interval has been added.

Parameters:

• Interval*: A string expression that is the interval you want to add
• Number: A numeric expression that is the number of intervals you want to add
• Date: The date to which the interval is added

*Interval: yyyy=Year, m=month, q=Quarter, d=Day

Example:

You need to add three months to the current date. You can do it in two ways:

• using the quarter as an interval and add 1

• using the month as an interval and add 3

Note that it is possible to use tokens and variables as the starting date. For example, this following formula will add a quarter to the current date:

MonthName and Month

These two formulas are also widely used as they allow you to extract the month and the month name based on a date. Again, remember to make sure that your input value is formatted with American format, otherwise, you will likely get an incorrect output. MonthName returns the name of a specified month. Month returns a number (1-12) representing the month of the year.

Month - Parameter:

• Date: Any expression that can represent a date

MonthName - Parameters:

• Month: The numeric designation of the month
• Abbreviate: A Boolean value that indicates if the month name is to be abbreviated – optional

Example:

If you need to extract a month name from a date, you need to first retrieve the month (number) and then based on the month number, you can retrieve the month name. It can be done using embedded formulas.

The first step is to create Month formula that extracts month number from the date. Press On hold to save the formula for later use:

Next step is to create MonthName formula that returns the name of the month we have got from the previous step (embedded formula):

The result is saved to a variable as an abbreviation. 

FormatDateTime

The Formatdatetime formula is another popular formula that you can use to format a date. It returns an expression formatted as a date or time.

Parameters:

• Date: A date expression to be formatted
• FormatNumber*: A number indicating the date/time format – optional

*FormatNumber: 0 – General Date (default), 1 – Long Date, 2 – Short Date, 3 – Long Time, 4 – Time using 24-hour format

Example:

You want a date to be displayed as Long Date. The formula looks like this:

Note that in the example [*DATE] token was used as the first parameter.