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.
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 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.
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.
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.
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.
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])
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])
DATEDIFF("day", [Job posting date created], [Date applied])
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.
Rise supports a wide range of 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 |
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” |
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:
Dateadd/Datediff available interval types:
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 |
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” |
Function | Description | Example | Result |
---|---|---|---|
RND() | Returns a random number [0,1) | RND() | e.g., 0.75391 |
Start simple - Begin with basic calculations before building complex formulas
Use meaningful names - When creating custom columns, give them clear, descriptive names
Format appropriately - Use formatting functions to make numbers and dates more readable
Consider NULL values - Use COALESCE or IF with ISNULL to handle missing data
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.