Write formulas in reports

Write formulas in reports

This is a detailed guide to writing reporting formulas in Rise. If you’re new to reporting, start with the basics:

Once you’re comfortable with the basics, learn how to add formula columns to reports. Use this guide as a companion reference.

Basic Formula Structure

If you’ve used Excel or Google Sheets before, you’ll find formulas in Rise to be familiar and easy to grasp.

A formula can consist of:

  • Constants (numbers, text in quotes)

  • Operators (+, -, *, /, >, <, =)

  • Tokens (in square brackets, like [Amount] or [Date]) that reference your report’s existing columns

  • And functions (like ROUND, SUM, IF)

For example: ROUND([Compensation rate] * 0.04, 2)

In an Employee Master List, this will create a custom formula column showing 4% of the compensation rate rounded to 2 decimal places.

Unlike Excel, Rise formulas don’t start with an equals sign (=).

Tokens in Report Formulas

Tokens represent existing columns in your report. For example:

  • [Preferred name] - References the employee Preferred Name column

  • [Amount] - References the Amount column

  • [Start date] - References the employee Start Data date column

Note that each report in Rise starts with a base report (e.g. Employee Master List, Pay Runs, Time Off Requests) and has its own columns you can reference. It’s not possible to reference columns from other reports. For example, it’s not possible to reference the Time Off Balance column in the Birthdays report.

How to find out the column token name

You can view available tokens by typing [ (left square bracket). Ensure correct spacing and capitalization—the token name must match exactly for the formula to work.

How tokens are used in formulas

Formula columns return a value for each row in your report. When a token is referenced, the formula retrieves the value from that column for the specific row.

You cannot reference values from other rows. For example, a formula using [Compensation rate] will return Bob’s rate for his row and Jane’s rate for hers. It cannot reference Jane’s rate in Bob’s row or calculate a sum across rows.

Creating Useful Report Columns

Here are some example formulas you might find helpful. Remember that they will only work in reports where the referenced columns are present. Use this for inspiration for your own formulas rather than copying and pasting them as they are.

Employee Master List

  • Identify temporary SINs (starting with 9): IF(LEFT([Government ID number], 1) = "9", "Temporary", "Permanent")

  • Concatenate Preferred and Last Names to get Full Name: CONCAT([Preferred name]," ",[Last name]

  • Calculate tenure in years based as of today: DATEDIFF("year", [Start date], TODAY())

  • Get the month and day from the complete Date of Birth: FORMATDATE([Date of birth], "MM-dd")

  • Identify employees with no phone numbers: AND(ISNULL([Cell phone]), ISNULL([Home phone]), ISNULL([Work phone]))

  • Convert hourly compensation to salaried (simple): IF([Compensation method] = "Hourly", [Compensation rate] * 2080, [Compensation rate])

Time Off Requests

  • Convert request duration to Hours if the unit is in Days: IF([Request duration unit] = "Days", [Request duration value] * 8, [Request duration value])

  • Adjust request duration for employees in United States: IF([Request start date] = "2025-02-17" AND [Country of employment] = "United States", [Request duration value] - 1, [Request duration value])

Applicants by Stage report

  • How many days after the job was posted did the candidate apply: DATEDIFF("day", [Job posting date created], [Date applied])

Pay Runs

  • Concatenate Pay Year and Pay Run Number, separated by a dash: CONCAT([Pay year], '-', [Pay run number]

If you have common examples clients love using, add them to the comments, and we’ll incorporate them into the documentation. Don’t need to write the actual formula, just the explanation of what it needs to do.

Supported Functions

Rise supports a wide range of functions.

Mathematical Functions

Function Description Example
ADD(a, b) Add values ADD(1, 2) → 3
SUBTRACT(a, b) Subtract b from a SUBTRACT(5, 3) → 2
MULTIPLY(a, b) Multiply values MULTIPLY(4, 3) → 12
DIVIDE(a, b) Divide a by b DIVIDE(10, 2) → 5
MOD(a, b) Remainder after division MOD(10, 3) → 1
ROUND(n, d) Round to decimals ROUND(3.14159, 2) → 3.14
CEIL(n) Round up CEIL(3.14) → 4
FLOOR(n) Round down FLOOR(3.89) → 3
INT(n) Truncate decimal INT(3.7) → 3
ABS(n) Absolute value ABS(-5) → 5
EXP(n) Exponential (eⁿ) EXP(2) → 7.389
POWER(a, b) a^b POWER(2, 3) → 8
SQRT(n) Square root SQRT(16) → 4
SGN(n) Sign (-1, 0, 1) SGN(-5) → -1
MIN(a, b, …) Smallest value MIN(1, 3, 2) → 1
MAX(a, b, …) Largest value MAX(1, 3, 2) → 3

Text / String Functions

Function Description Example Result
CONCAT(t1, t2, …) Join text CONCAT(“Hello”, " ", “World”) “Hello World”
UPPER(text) / UCASE Convert to uppercase UPPER(“sales”) “SALES”
LOWER(text) / LCASE Convert to lowercase LOWER(“HR”) “hr”
TITLECASE(text) / PROPER Capitalize each word TITLECASE(“john doe”) “John Doe”
TRIM(text) Remove spaces TRIM(" team lead ") “team lead”
LTRIM(text) Remove leading spaces LTRIM(" text") “text”
RTRIM(text) Remove trailing spaces RTRIM("text ") “text”
LEFT(text, n) First n characters LEFT(“Finance”, 3) “Fin”
RIGHT(text, n) Last n characters RIGHT(“Finance”, 4) “ance”
MID(text, start, length) Extract part of text MID(“Employee”, 2, 3) “mpl”
SUBSTRING(text, s, l) Alias of MID SUBSTRING(“Welcome”, 1, 4) “Welc”
LEN(text) Length of text LEN(“Rise”) 4
FIND(substr, text) Position of substring FIND(“s”, “Results”) 3
INSTR(substr, text) First occurrence INSTR(“banana”, “n”) 3
INSTRREV(text, substr) Last occurrence INSTRREV(“banana”, “n”) 5
CONTAINS(text, substr) Contains substring? CONTAINS(“admin,finance”, “admin”) true
ISBLANK(value) Is value blank? ISBLANK("") true
SUBSTITUTE(text, f, r) Replace text SUBSTITUTE(“green apple”, “green”, “red”) “red apple”
REPLACE(text, f, r) Alias of SUBSTITUTE REPLACE(“cat”, “c”, “b”) “bat”
STRREVERSE(text) Reverse string STRREVERSE(“abc”) “cba”
SPACE(n) Spaces CONCAT(“Start”, SPACE(3), “End”) “Start End”
STRING(n, character) Repeat character STRING(4, “*”) “****”
PADLEFT(text, number, character) Pad left with char PADLEFT(“7”, 3, “0”) “007”
PADRIGHT(text, number, character) Pad right with char PADRIGHT(“7”, 3, “0”) “700”

Date Functions

Function Description Example Result
TODAY() / DATE() Current date TODAY() “2025-03-25”
NOW() Current timestamp NOW() “2025-03-25 14:45”
DATESERIAL(number, number, number) Create date from parts DATESERIAL(2025, 3, 25) “2025-03-25”
DATEADD(interval type, number, date) Add intervals to the date DATEADD(“m”, 2, “2025-03-01”) “2025-05-01”
DATEDIFF(interval type, date, date) Days difference DATEDIFF(“d”, “2025-01-01”, “2025-03-25”) 83
DATEPART(interval type, date) Extract date parts from date DATEPART(“y”, “2025-03-25”) 2025
YEAR(date) Extract year YEAR(“2025-03-25”) 2025
MONTH(date) Extract month MONTH(“2025-03-25”) 3
DAY(date) Extract day DAY(“2025-03-25”) 25
HOUR(timestamp) Extract hour HOUR(“2025-03-25 14:45:00”) 14
MINUTE(timestamp) Extract minute MINUTE(“2025-03-25 14:45:00”) 45
SECOND(timestamp) Extract second SECOND(“2025-03-25 14:45:00”) 0
WEEK(date) Week of year WEEK(“2025-03-25”) 13
DOY(date) Day of year DOY(“2025-03-25”) 84
QUARTER(date) Quarter QUARTER(“2025-03-25”) 1
FORMATDATE(date, format) Format custom date FORMATDATE(“2025-03-25”, “YYYY/mm/dd”) “2025/03/25”
MONTHNAME(date) Get month name MONTHNAME(“2025-03-25”) “March”
WEEKDAYNAME(date) Get weekday name WEEKDAYNAME(“2025-03-25”) “Tuesday”
ISDATE(string) Check if valid date ISDATE(“2025-03-25”) true

Datepart available interval types:

  • yyyy, y, year
  • q, quarter
  • m, month
  • w, week
  • d, day
  • h, hour
  • m, minute
  • s, second
  • dow, weekday
  • doy

Dateadd/Datediff available interval types:

  • yyyy, y, year
  • q, quarter
  • m, month
  • w, week
  • d, day
  • h, hour
  • m, minute
  • s, second

Logical Functions

Function Description Example Result
IF(cond, t, f) / IIF Conditional logic IF([Amount] > 1000, “High”, “Low”) “High” / “Low”
IFS(…) First true condition IFS([Amount]>1000, “High”, [Amount]>500, “Medium”, true, “Low”) “High” or “Medium”
SWITCH(val, m1, r1, …, default) Multi-way branch SWITCH([Status], “A”, “Active”, “P”, “Pending”, “Other”) “Active”
AND(c1, c2) All true? AND([Active] = true, [Has access] = true) true or false
OR(c1, c2) Any true? OR([Type]=“Manager”, [Seniority]=“Senior”) true or false
XOR(c1, c2) One true only? XOR(true, false) true
NOT(condition) Negate boolean NOT([Is terminated]) true / false
ISEQUAL(v1, v2) Are equal ISEQUAL([Region], “West”) true / false
ISNULL(value) Is value NULL? ISNULL([Email]) true / false
ISBLANK(value) Is value a blank string? ISBLANK([Note]) true / false
ISDATE(value) Is value a valid date? ISDATE(“not-a-date”) false
ISNUMERIC(value) Is value numeric? ISNUMERIC(“123.45”) true
ISTEXT(value) Is value a text? ISTEXT(“Hello”) true
ISNUMBER(value) Is number? ISNUMBER(42) true
QUARTER(date) Quarter QUARTER(“2025-03-25”) 1
FORMATDATE(date, format) Format custom date FORMATDATE(“2025-03-25”, “YYYY/mm/dd”) “2025/03/25”
MONTHNAME(date) Get month name MONTHNAME(“2025-03-25”) “March”
WEEKDAYNAME(date) Get weekday name WEEKDAYNAME(“2025-03-25”) “Tuesday”
ISDATE(string) Check if valid date ISDATE(“2025-03-25”) true

Formatting Functions

Function Description Example Result
FORMAT_NUMBER(number, decimal point) Commas + decimals FORMAT_NUMBER(1234.567, 2) “1,234.57”
FORMAT_CURRENCY(number) Currency format FORMAT_CURRENCY(1234.56) “$1,234.56”
FORMAT_PERCENT(number) Percentage format FORMAT_PERCENT(0.1234) “12.34%”
FORMAT_DATE(date, format) Format date FORMAT_DATE(“2025-03-25”, “YYYY-MM-DD”) “2025-03-25”
FORMAT_TIME(time, format) Format time FORMAT_TIME(“14:30:00”, “HH:MM”) “14:30”
FORMAT_DATETIME(timestamp, format) Format datetime FORMAT_DATETIME(“2025-03-25 14:30”, “vbGeneralDate”) “3/25/2025 2:30 PM”
NOT(condition) Negate boolean NOT([Is terminated]) true / false
ISEQUAL(v1, v2) Are equal ISEQUAL([Region], “West”) true / false
ISNULL(value) Is value NULL? ISNULL([Email]) true / false
ISBLANK(value) Is value a blank string? ISBLANK([Note]) true / false
ISDATE(value) Is value a valid date? ISDATE(“not-a-date”) false
ISNUMERIC(value) Is value numeric? ISNUMERIC(“123.45”) true
ISTEXT(value) Is value a text? ISTEXT(“Hello”) true
ISNUMBER(value) Is number? ISNUMBER(42) true

Date Format Constants Examples:

Format Example Result Result
vbShortDate FORMAT_DATE(“2025-03-25”, “vbShortDate”) “3/25/2025” “1,234.57”
vbLongDate FORMAT_DATE(“2025-03-25”, “vbLongDate”) “Tuesday, March 25, 2025” “$1,234.56”
vbShortTime FORMAT_TIME(“14:30:00”, “vbShortTime”) “14:30” “12.34%”
vbGeneralDate FORMAT_DATETIME(“2025-03-25 14:30”, “vbGeneralDate”) “3/25/2025 14:30:00” “2025-03-25”
YYYY-MM-DD FORMAT_DATE(“2025-03-25”, “YYYY-MM-DD”) “2025-03-25” “14:30”
HH24:MI:SS FORMAT_TIME(“14:30:45”, “HH24:MI:SS”) “14:30:45” “3/25/2025 2:30 PM”
YYYY-MM-DD HH:MI:SS FORMAT_DATETIME(“2024-03-15T14:30:45”, “YYYY-MM-DD HH:MI:SS”) “2024-03-15 02:30:45”

Miscellaneous Functions

Function Description Example Result
RND() Returns a random number [0,1) RND() e.g., 0.75391

Tips for Building Effective Report Formulas

  1. Start simple - Begin with basic calculations before building complex formulas

  2. Use meaningful names - When creating custom columns, give them clear, descriptive names

  3. Format appropriately - Use formatting functions to make numbers and dates more readable

  4. Consider NULL values - Use COALESCE or IF with ISNULL to handle missing data

Troubleshooting Report Formulas

  • My formula shows errors: Check for missing symbols, unmatched parentheses, or incorrect function names.

  • Results show #ERROR: Verify you’re not dividing by zero or using incompatible data types

  • Numbers don’t match expectations: Confirm the order of operations with parentheses

  • Dates are incorrect: Ensure date formats match your data source format

Remember that each token with [ ] represents an existing column in your report data. If you don’t see the expected result, verify that the column exists and contains the type of data you expect.


    • Related Articles

    • Add formula columns to reports

      This guide will help you create powerful custom columns in your reports using formulas. With formulas, you can transform, calculate, and display data exactly how you need it, going beyond the standard columns available by default. What are formula ...
    • Summarize and group report data

      This feature is available only on the latest version of Rise. If reports are missing from your navigation, check this article for migration steps. It’s free, takes less than a minute, and unlocks new features for your organization. Rise reporting ...
    • Access reports

      To access reports, click the Report icon in the left hand side menu. This feature is available only on the latest version of Rise. If reports are missing from your navigation, check this article for migration steps. It’s free, takes less than a ...
    • View and edit a report

      This feature is available only on the latest version of Rise. If reports are missing from your navigation, check this article for migration steps. It’s free, takes less than a minute, and unlocks new features for your organization. Reports are broken ...
    • Save a customized report

      This feature is available only on the latest version of Rise. If reports are missing from your navigation, check this article for migration steps. It’s free, takes less than a minute, and unlocks new features for your organization.After you make ...