---
title: "Calculated fields syntax"
slug: "calculated-fields-syntax"
description: "Description of operators, syntax examples, and syntax descriptions including DATE( ) formulas, to help create formulaic expressions for calculated fields in a crediting rule."
updated: 2026-02-26T18:19:42Z
published: 2026-02-26T18:19:42Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.fullcast.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Calculated fields syntax

## Operators

#### Arithmetic operators

Used to perform mathematical calculations.

| Operator | Description |
| --- | --- |
| `+` | Addition operator sums or concatenates two values. |
| `-` | Subscription operator subtracts the right value from the left value. |
| `*` | Multiplication operator multiplies two numbers. |
| `/` | Division operator divides the left operand by the right. |

#### Comparison (relational) operator

Evaluates the relationship between two values.

| Operator | Description |
| --- | --- |
| `<` | Less than |
| `>` | Greater than |
| `==` | Equals |
| `!=` | Not equal to |

#### Assignment and logical operators

Set values or combine multiple conditions.

| Operator | Description |
| --- | --- |
| `=` | Assignment operator assigns a value. |
| `&` | AND operator returns `True` only if both conditions are true. |
| `\|` | OR operator returns `True` if at least one condition is true. |

#### Brackets and delimiters

Define the scope, priority, and data structure in the syntax.

| Operator | Description |
| --- | --- |
| `( )` | Circular open and close brackets: - **Circular:** Changes order of operations (parentheses first). - **Logic:** Groups conditions in an `IF` statement. - **Functions:** Contains arguments, for example, `print("Hello")`. |
| `{ }` | Curly open and close braces define a block of code. Everything inside the braces belongs together (like the body of a function or a loop). |
| `[ ]` | Square open and close brackets: - Commonly used for arrays or lists. - Indexes to get a specific item (for example, `list[0]` gets the first item). |

#### Control flow (conditionals)

Keywords that direct the path the program takes based on the results of the comparison operators.

| Operator | Description |
| --- | --- |
| `IF` | The starting point of a decision. It evaluates a condition. |
| `ELSE` | Marks the block of code that runs only if the `IF` condition is `True`. |
| `THEN` | Runs the block of syntax only if the `IF` condition is `False`. |

## Calculated field data types

When you create a calculated field, you have to select the data type for that field which determines how data is handled and how it displays in the transactions table.

#### Data types

The following is a description of the available data types you can select for a calculated fields.

| Data type | Description | Use case | Example syntax |
| --- | --- | --- | --- |
| **Date** | A calendar date value without a time component. | Compare or return dates from CRM fields such as close dates or contract start dates. | `IF Accounts@LastModifiedDate > DATE("today") then "Active" else "Overdue"` |
| **Date & Time** | A combined date and time value with full timestamp precision. | Capture or compare timestamps where time of day matters, such as record creation or last modification. | `DATE("now", "Y-m-d H:i:s")` |
| **Decimal Number (0.00)** | A numeric value with decimal precision, typically two decimal places. | Calculate financial values such as commission amounts, percentages, or rates. | `Opportunities@Amount * 0.15` |
| **Rounded Number** | A numeric value rounded to the nearest whole integer. | Return whole-number results such as headcount, quantities, or unit counts. | `Opportunities@Quantity * 2` |
| **Month From Date** | The numeric month (1–12) extracted from a date field. | Group or filter transactions by month for period-based reporting. | Input: `2026-02-20` > Output: `2` |
| **Month Name From Date** | The full month name (February) extracted from a date field. | Display readable month labels in commission reports, ledger views, or exports. | Input: `2026-02-08` > Output: `February` |
| **Paragraph Text** | Multi-line or longer text output from a formula. | Return descriptive labels, concatenated values, or conditional messages that may span multiple lines. | `IF Amount > 10000 then "High Value Deal - Requires Manager Approval" else "Standard Processing"` |
| **Year From Date** | The four-digit year extracted from a date field. | Group transactions by fiscal year for annual reporting or year-over-year comparisons. | Input: `2026-02-20` > Output: `2026` |

## Sample formulas

The following table provides examples of different common formulas and their required syntax that you can use to create calculated fields.

#### **Concatenation**

Add two values together or combine two separate strings into a single string.

- **Formula:**Field1 + Field2
- **Syntax:** `tablename@Field1 + tablename@Field2`

#### **Empty value handling**

Check if a field is blank to provide a default value, for example, Default Multiplier.

- **Formula:**If Field1 is empty, then 1, else 2
- **Syntax:**`if ( tablename@Field1 == "" ) then 1 else 2` (There is no comma after the 1 in the syntax.)

#### **Discrepancy check**

Flag records where two fields do not match for auditing purposes.

- **Formula:**If Field1 does not equal Field2, then YES, else NO
- **Syntax:**`if ( tablename@Field1 != tablename@Field2 ) then "YES" else "NO"` (This treats YES and NO as strings.)

#### **Date-based rate shift**

Apply different multipliers based on a specific cutoff date.

- **Formula:**If Field1 date is greater than 25/12/2020 then Field2 * 0.23, else Field2* 0.21
- **Syntax:**`if (tablename@Field1 > "2020-12-25") then tablename@Field2*0.23 else tablename@Field2* 0.21`

#### **Nested conditions**

Create a multi-tiered result based on the status of two different fields.

- **Formula:**If Field1 = YES, then If( field2 = No, then Red, else Blue), else Green
- **Syntax:**`if ( tablename@Field1 == "YES" ) then ( if (tablename@Field2 == "NO" ) then "Red" else "blue" ) else "Green"`

#### Compound AND requirement

Require both conditions to be met to trigger a specific result.

- **Formula:**If Field1 = YES and Field2 = NO, then Red, else Green
- **Syntax:**`if ( ( tablename@Field1 == "YES" ) & ( tablename@Field2 == "NO" ) ) then "Red" else "Green"`

#### **Complex OR with date math**

Require both conditions to be met to trigger a specific result.

- **Formula:**If Field1 = YES or Field3 is less than 24/07/2020, then Field3 + 3 Months + ( Field4 + 3 ) Years, else Field3
- **Syntax:**`if ( ( tablename@Field1 == "YES" ) | ( tablename@Field3 < "2020-07-24" ) ) then tablename@Field3 + ( 3 Months ) + ( ( tablename@Field4 + 3 ) Years ) else tablename@Field3`

## DATE( ) formula

Use the following as a reference when you create DATE ( ) expressions for calculated fields. There are three parameters you can use with the DATE( ) formula:

- **Expression on date:** A dynamic calculation or logical rule to select a specific point in time based on relative variables rather than a fixed, static value.
- **Date format:** Rearranges the date data into the layout you indicate.
- **Date field:** A specific object field used to select a specific point in time.

Examples of parameter input combinations:

- `DATE( <Expression_On_Date> )`
- `DATE( <Expression_On_Date> , <Date_Format> )`
- `DATE( <Date_Field> , <Date_Format> )`
- `DATE( <Expression_On_Date> , <Date_Field> )`
- `DATE( <Expression_On_Date> , <Date_Field> , <Date_Format> )`

#### Date format syntax references for DATE( ) formula

The following syntax is used with the `DATE( )` formula to format the date output for calculated fields.

> [!NOTE]
> Note
> 
> Date format only works with the paragraph text [data type](/plan-and-pay/docs/calculated-fields-syntax#calculated-field-data-types).

| Syntax | Description | Output example |
| --- | --- | --- |
| `Y` | Indicates 4-digit year | `2026` |
| `y` | Indicates a 2-digit year | `26` |
| `F` | Full text for month | `February` |
| `M` | Short 3-letter text indicating month | `Feb` |
| `m` | Two digits indicating month with leading zero | `02` |
| `n` | Single digit indicating month with no leading zeros | `2` |
| `d` | Two digits indicating day with leading zeros | `05` |
| `j` | Single digit indicating day without leading zeros | `5` |
| `l` | Full text indicating weekday | `Tuesday` |
| `D` | Short 3-letter text indicating weekday | `Tue` |
| `H` | 24-hour format indicating time | `14` (for 2 pm) |
| `h` | 12-hour format indicating time | `2` (usually proceeded with am/pm) |
| `i` | Two digits indicating minutes | `27` |
| `s` | Two digits indicating seconds | `31` |
| `a` | Lowercase am or pm indicating time of date | 2:27 `pm` |
| `L` | Boolean that indicates whether the year is a leap year or not. | `0` (False) or `1` (True) |
| `t` | Number indicating the total number of days in the month | `28` (for Feb 2026) |

#### Expression on date syntax references for DATE( ) formula

The expression on date calculates a specific point in time calculated from the current date (if no date field parameter is included) or the date in the field from the date field parameter.

| Expression on date | Description | Examples | Output example |
| --- | --- | --- | --- |
| Basic expressions | Uses the current system time as the starting point. | `DATE ( “now” )` | 02/23/2026 if the current date is 23 February 2026. |
| Boundary expressions | Calculates the edge of the time period (month or year). | `DATE ( “first day of this month”)` | 02/01/2026 if the current month is February. |
| Offset expressions | Calculates the time by moving forward (positive) or backward (negative) from the indicated date. | **Forward:** `DATE ( "first day of 2 months" )` **Backward:** `DATE ( "first day of -2 months" )` | If the month is February: **Forward:** 04/01/2026 **Backward:** 12/01/2025 |

#### Syntax scenarios for DATE( ) formula

The following are examples of common syntax used for the `DATE( )` formula. When you use the DATE( ) formula, you must select either **Date**, **Date & Time**, or **Paragraph Text** as the data type based on the expected output you need.

> [!NOTE]
> Note
> 
> Ensure the syntax is typed with the appropriate spacing otherwise it will throw an error.

| Syntax example | Required data type | Expected output format |
| --- | --- | --- |
| `DATE ( “` `” )` | **Date** | **MM/DD/YYYY** Example, 02/19/2026 if today was 19 February 2026. |
| `DATE ( "now" )` | **Date**or **Date & Time** | **MM/DD/YYYY** or **MM/DD/YYYY HH:MM:SS** Example, 02/17/2026 if you select DATE data type or 02/17/2026 14:27:31 if you select DATETIME data type and the exact moment is 17 February 2026 at 14:47:31. |
| `DATE ( "last day of this month" )` | **Date** | **MM/DD/YYYY** Example, 02/28/2026 for last day of February. |
| `DATE ( "first day of this month" )` | **Date** | **MM/DD/YYYY** Example, 02/01/2026 if the current month is February. |
| `DATE ( "last day of next year" )` | **Date** | **MM/DD/YYYY** Example, 02/28/2027 if the current month is February 2026. |
| `DATE ( "last day of December next year" )` | **Date** | **MM/DD/YYYY** Example, 12/31/2027 if the current year is 2026. |
| `DATE ( "first day of next month" )` | **Date** | **MM/DD/YYYY** Example, 03/01/2026 if the current month is February 2026. |
| `DATE ( "last day of next month" )` | **Date** | **MM/DD/YYYY** Example, 03/31/2026 if the current month is February 2026. |
| `DATE ("first day of 2 months" )` | **Date** | **MM/DD/YYYY** Example, 04/01/2026 if the current month is February 2026. |
| `DATE ( "last day of -2 months" )` | **Date** | **MM/DD/YYYY** Example, 12/31/2025 if the current month is February 2026. |
| `DATE ( "now" , "Y-m-d" )` | **Date** | **YYYY/MM/DD** Example, 2026/02/17 if the current date is 17 February 2026. |
| `DATE ( "last day of next month" , Opportunities@Opportunities_CloseDate )` | **Date** | **MM/DD/YYYY** Example, 03/31/2026 if the opportunity close date is February 2026. |
| `DATE ( "last day of -2 months" , Opportunities@Opportunities_CloseDate )` | **Date** | **MM/DD/YYYY** Example, 12/31/2025 if the opportunity close date is February 2026. |
| `DATE ( Opportunities@Opportunities_CloseDate )` | **Date** | **MM/DD/YYYY** Example, 12/31/2025 if the opportunity close date is 31 February 2026. |
| `DATE ( "now" , "l , F j , Y" )` | **Paragraph Text** | **Weekday, Month Day, Year** Example, Thursday , February 19 , 2026. |
| `DATE ( "2025-12-15 08:30:23")` | **Date & Time** | **MM/DD/YYYY HH:MM:SS** Example, 12/15/2025 08:30:23 if the system clock is US timezone. |
| `DATE ( "last day of -2 months")` | **Date** | **MM/DD/YYYY** Example, 12/31/2025 if the current month is February 2026. |
| `DATE ( "2025-07-15" , "F Y" )` | **Paragraph Text** | **Month YYYY** Example, July 2026. |
| `DATE ( "last day of -2 months" , "2025-11-15" , "D, d M Y" )` | **Paragraph Text** | **Weekday (short) , Day Month (short) , YYYY** Example, Tues , 30 Sept , 2025 based on specified date of 15 November 2025. |
| `IF ( Opportunities@Opportunities_CloseDate > DATE ( "today" ) ) then 500 else 200` | **Paragraph Text** | If the opportunity close date is later than today (future opportunity), the output is **200**. If the opportunity close date is today or before (past opportunity), the output is **500**. |