The following formula functions are currently supported in Workiva. You can also view these functions using the Formula Assistant in the Formula Bar.
Contents:
- Date functions
- Financial calculation functions
- Information gathering functions
- Lookup functions
- Logical functions
- Mathematical functions
- Rounding functions
- Text creation functions
- Statistical functions
| Function | Description | Syntax | Example | Visual |
| DATE | Returns a date from a given year, month, and day |
DATE(year,month,day)
|
A formula formatted as DATE(2021,10,14) returns 10/14/2021. |  |
| DATEVALUE | Returns a serial number based on a date written in MM/DD/YYYY format |
DATEVALUE("date")
|
A formula formatted as DATEVALUE("03/06/2021") returns the serial number of the date, 44261. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| DAY | Returns the day of the month for a date |
DAY("date")
|
A formula formatted as DAY(A3), referencing a cell that contains the date 06/29/2021, will ignore the month and year and will only return the date, 29. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| DAYS | Returns the number of days between two dates |
DAYS(end_date,start_date)
|
A formula formatted as DAYS(A4,A3), referencing cells that contain the dates 6/29/021 and 6/10/2021 respectively, will display the total number of days between these dates. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| DAYS360 | Returns the number of days between two dates based on a 360-day year (twelve 30-day months) |
DAYS360(end_date, start_date, [use_european_method])
|
A formula formatted as DAYS360(A3,A4), referencing cells that contain the dates 2/25/2021 and 3/01/2021 respectively, will display the number of days between them as 6 rather than 4; this is because a month is treated as 30 days, regardless of how long a month is. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| EDATE | Returns the serial date that is the indicated number of months before or after a specified date |
EDATE(start_date, months)
|
A formula formatted as EDATE(A3,3) will return a date that is three months after the date entered in cell A3; in this case, the reference cell date is 6/29/2021, so the formula cell will display 9/29/2021. |  |
| EO MONTH | Returns the end of the month for a given date, offset by a number of months |
EOMONTH(date, months)
|
A formula formatted as EOMONTH(A3,3) will return a date that is the last day of the month, three months after the date entered in cell A3; in this case, the reference cell date is 6/29/2021, so the formula cell will display 9/30/2021. |  |
| MONTH | Returns the month of the year for a date |
MONTH(date)
|
A formula formatted as MONTH(A3), referencing a cell that contains the date 6/29/2021, will return the number 6, as June is the 6th month. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| NETWORKDAYS | Returns the number of whole working days between two dates |
NETWORKDAYS(start_date, end_date, [holidays])
|
A formula formatted as NETWORKDAYS(A3,A4,A5), referencing cells that contain the dates 11/19/2021, 12/03/2021, and 11/25/2021 respectively, will result in the number 10. The formula returns the total number of business days between the dates, with 11/25/2021 excluded as a special holiday, even though it falls on a Thursday. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| NETWORKDAYS.INTL | Returns the number of whole working days between two dates with custom weekends and holidays |
NETWORKDATSINTL(start_date, end_date, [weekend], [holidays])
|
A formula formatted as NETWORKDAYS.INTL(A3,A4,12,A5). referencing cells that contain the dates 11/19/2021, 12/03/2021, and 11/25/2021 respectively, will result in the number 12. The formula returns the total number of business days between the dates, with the third number in the formula indicating that every day except Monday is considered a business day, with 11/25/2021 excluded as a special holiday. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| WEEKDAY | Returns the day of the week corresponding to a date |
WEEKDAY (serial_number, [return_type])
|
A formula formatted as WEEKDAY(A3), referencing a cell that contains the date 6/29/2021, will result in the number three. Because this date is a Tuesday, which is the third day of the week if the week starts on Sunday, A1 returns the number three. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
| WORKDAY | Returns the serial date before or after a specified number of workdays |
WORKDAY(start_date, days, [holidays])
|
A formula formatted as WORKDAY(A3,A4,A5), referencing cells that contain the values 6/29/2021, 12, and 7/05/2021 respectively, will result in the date 7/16/2021. Cell A3 contains the start date, Cell A4 indicates that we count 12 business days (Monday-Friday), and cell A5 indicates that we should not count 7/05/2021 as one of those business days, as it is being treated as a holiday. |  |
| WORKDAY.INTL | Returns the serial date before or after a specified number of workdays with custom weekend parameters |
WORKDAY.INTL(start_date, days, [weekend], [holidays])
|
A formula formatted as WORKDAY.INTL(A3,A4,A5,A6), referencing cells that contain the values 6/29/2021, 12, 5, and 7/04/2021 respectively, will result in the date 7/18/2021. Cell A3 indicates the start date as 6/29/2021, and cell A4 indicates that we will be counting 12 business days. In this case, A5 indicates that weekends will be Wednesday-Thursday, so all other days will be business days, except 7/04/2021 in Cell A6, which is an excluded holiday. |  |
| YEAR | Returns the year for a date |
YEAR(date)
|
A formula formatted as YEAR(A3), referencing a cell containing the date 6/29/2021, will result in the year 2021, as the date occurs in this year. The value is only returning the year rather than the full date, so the cell containing the function should not use the 'date' format. |  |
| YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates |
YEARFRAC(start_date, end_date, [basis])
|
A formula formatted as YEARFRAC(A3,A4), referencing cells that contain the dates 6/29/2021 and 12/29/2021 respectively, will result in the value .5. Cell A1 displays the part of a year that elapses from the date in A3 to the date in A4. Since the basis variable has been excluded, the formula assumes 12 thirty-day months in one year, resulting in exactly six months or half a year. The value is returned as a number rather than a date, so the cell containing the function should not use the 'date' format. |  |
Financial calculation functions
| Function | Description |
| FV | Calculates the future value of an investment based on constant interest rate |
| IRR | Calculates internal rate of return for a regular series of cash flows |
| NPV | Calculates the net present value of an investment using discount rates, payments, and income * |
| PMT | Calculates payment on a loan given constant interest rate and payments |
| PV | Calculates the present value of an investment based on a constant interest rate. |
Information gathering functions
| Function | Description |
| COUNT | Counts the number of numeric values in a set of values * |
| COUNTA | Counts the number of non-blank values in a set of values * |
| COUNTBLANK | Counts the number of blank values in a set of values * |
| COUNTIF | Counts the number of cells in a range that meet a certain condition |
| COUNTIFS | Counts the number of cells in a range that meet a set of conditions |
| ISBLANK | Returns TRUE or FALSE if the selection is empty |
| ISERROR | Returns TRUE if the value is an error; otherwise returns FALSE |
| ISNA | Returns TRUE or FALSE if the selection is #N/A |
| ISNUMBER | Returns TRUE or False if the selection is a number |
| VALUE | Returns a numerical value for a given text string representing a number or date |
| Function | Description |
| CELL | Returns information about a cell reference |
| CHILDREFS | References selected range for each child sheet ** |
| HLOOKUP | Finds given value in a row then returns a value from that column |
| INDEX | Returns the value at the specified row/column coordinate |
| MATCH | Finds an index in a one-dimensional range |
| VLOOKUP | Finds given value in a column then returns a value from that row |
| XLOOKUP | Finds an index in the lookup range and returns the corresponding value in the return range |
| XMATCH | Finds a value in the lookup range and returns the corresponding index |
** Must be used within another function
| Function | Description |
| AND | Returns TRUE if all arguments are TRUE; otherwise returns FALSE * |
| CHOOSE | Uses index_num to return a value from the list of value arguments * |
| IF | Returns one value if a condition specified is TRUE and another value if it is FALSE * |
| IFERROR | Returns value if it is not an error; otherwise, returns value_if_error |
| IFNA | Returns an alternate value if the first value is #N/A |
| IFS | Checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition * |
| NOT | Returns FALSE if the expression is TRUE; Returns TRUE if the expression is FALSE |
| OR | Returns TRUE if any arguments are TRUE; returns FALSE if all arguments are FALSE * |
| Function | Description |
| ABS | Returns the absolute value of a number |
| EXP | Returns e raised to the power of number |
| LN | Returns the natural logarithm of a number |
| MOD | Returns the remainder from a division |
| POWER | Returns the result of an exponentiation |
| PRODUCT | Multiplies all the numbers in a selection of cells * |
| SIGN | Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative |
| SQRT | Returns a positive square root |
| SUBTOTAL | Create a total from given values using the specified operation, ignoring any subtotals in the range |
| SUM | Adds all the numbers in a selection of cells * |
| SUMIF | Adds the cells according to a condition in a corresponding range |
| SUMIFS | Adds the cells according to a set of conditions in corresponding ranges |
| SUMPRODUCT | Multiplies corresponding components in equal-sized ranges and returns the sum of the products |
| Function | Description |
| INT | Rounds a number down to the nearest integer |
| MROUND | Returns a number rounded to the desired multiple |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Rounds a number down |
| ROUNDUP | Rounds a number up |
| TRUNC | Truncates a number to a specific number of digits |
| Function | Description |
| CODE | Returns the numeric code of the first character in the given text |
| CONCATENATE | Joins several text strings into one text string * |
| CONCATENATEIF | Joins cells with a separator according to a condition in a range |
| CHAR | Returns the character associated with the given character code |
| FIND | Returns the starting position of text within a string |
| LEFT | Returns the first character or characters in some text |
| LEN | Returns the number of characters in the argument |
| LOWER | Returns the text with all characters in lowercase |
| MID | Returns a selected portion of text |
| PROPER | Returns the text with only the first letter in each word capitalized |
| REPT | Repeats text a given number of times |
| RIGHT | Returns the last character in some text |
| SEARCH | Returns the starting position of text within a string |
| SUBSTITUTE | Substitutes specific characters of the text with new text |
| TEXT | Formats the given number as text by the format string |
| TEXTJOIN | Combines text from multiple ranges with a delimiter between each text value * |
| TRIM | Removes excess whitespace from text |
| UNICHAR | Returns the character associated with the given Unicode number |
| UNICODE | Returns the Unicode number of the first character in the given text |
| UPPER | Returns the text with all characters capitalized |
| Function | Description |
| AVERAGE | Returns the average (arithmetic mean) of a set * |
| AVERAGEA | Finds the arithmetic mean of a set including text and logical values * |
| AVERAGEIFS | Returns the average of all cells that meet multiple criteria |
| LARGE | Returns the n-th largest value from a range * |
| MAX | Returns the largest number in a set of values * |
| MAXA | Returns the largest value in a set including text and logical values * |
| MAXIFS | Returns the maximum value of all cells that meet multiple criteria |
| MEDIAN | Returns the statistical median of a given set of numbers * |
| MIN | Returns the smallest number in a set of values * |
| MINA | Returns the smallest value in a set including text and logical values * |
| MINIFS | Returns the minimum value of all cells that meet multiple criteria |
| PERCENTILE | Returns a percentile of values in a cell range |
| PERCENTILE.EXC | Returns a percentile of values (0-1, exclusive) in a cell range |
| PERCENTILE.INC | Returns a percentile of values (0-1, inclusive) in a cell range |
| QUARTILE | Returns a quartile of values in a cell range |
| QUARTILE.EXC | Returns a quartile of values (0-4, exclusive) in a cell range |
| QUARTILE.INC | Returns a quartile of values (0-4, inclusive) in a cell range |
| RANK | Returns the rank of a given value in a corresponding range * |
| RANK.AVG | Returns the statistical average of the rank of a given value in a corresponding range * |
| RANK.EQ | Returns the statistical rank of a given value in a corresponding range |
| SMALL | Returns the n-th smallest value from a range * |
| STDEV | Returns the standard deviation for a given set of values within a sample * |
| STDEVA | Returns the standard deviation for a given set of values within a sample, where text or FALSE=0 and TRUE=1 * |
| STDEVPA | Returns the standard deviation for a given set of values within an entire population, where text or FALSE=0 and TRUE=1 * |
| STDEV.S | Returns the standard deviation for a given set of values within a sample * |
| STDEV.P | Returns the sample standard deviation for a given set of values for an entire population * |
* Can be used with CHILDREFS function