The following sections list the XpandedReports functions by category.
Most of the functions are similar to those provided by Excel. The Excel type functions display Excel help when selected.
Date and time functions
- DATE: Returns the serial number of a particular date
- DATEDIF: Calculates the number of days, months, or years between two dates
- DATEVALUE: Converts a date in the form of text to a serial number
- DAY: Converts a serial number to a day of the month
- DAYS360: Calculates the number of days between two dates based on a 360-day year
- DURATION: Converts a QuickBooks duration field to hours.
- HOUR: Converts a serial number to an hour
- MINUTE: Converts a serial number to a minute
- MONTH: Converts a serial number to a month
- NETWORKDAYS: Returns the number of whole workdays between two dates
- NOW: Returns the serial number of the current date and time
- SECOND: Converts a serial number to a second
- TIME: Returns the serial number of a particular time
- TIMEVALUE: Converts a time in the form of text to a serial number
- TODAY: Returns the serial number of today's date
- WEEKDAY: Converts a serial number to a day of the week
- WEEKNUM: Converts a serial number to a number representing where the week falls numerically with a year
- WORKDAY: Returns the serial number of the date before or after a specified number of workdays
- YEAR: Converts a serial number to a year
- YEARFRAC: Returns the year fraction representing the number of whole days between start_date and end_date
Information functions
- ISBLANK: Returns TRUE if the value is blank
- ISEVEN: Returns TRUE if the number is even
- ISLOGICAL: Returns TRUE if the value is a logical value
- ISNONTEXT: Returns TRUE if the value is not text
- ISNUMBER: Returns TRUE if the value is a number
- ISODD: Returns TRUE if the number is odd
- ISTEXT: Returns TRUE if the value is text
- N: Returns a value converted to a number
- TYPE: Returns a number indicating the data type of a value
Logical functions
- AND: Returns TRUE if all of its arguments are TRUE
- FALSE: Returns the logical value FALSE
- IF: Specifies a logical test to perform
- NOT: Reverses the logic of its argument
- OR: Returns TRUE if any argument is TRUE
- TRUE: Returns the logical value TRUE
Math functions
Note: Some functions are supported only on the Group Formula section of the Display tab, and NOT for Computed Columns. [Group] indicates a Group Formula section, and not on a Computed Columns.- ABS: Returns the absolute value of a number
- CEILING: Rounds a number to the nearest integer or to the nearest multiple of significance
- EVEN: Rounds a number up to the nearest even integer
- EXP: Returns e raised to the power of a given number
- FACT: Returns the factorial of a number
- FIRST [Group]: The first value in a sequence
- FLOOR: Rounds a number down, toward zero
- INT: Rounds a number down to the nearest integer
- LAST [Group]: The last value in a sequence
- LN: Returns the natural logarithm of a number
- LOG: Returns the logarithm of a number to a specified base
- LOG10: Returns the base-10 logarithm of a number
- MOD: Returns the remainder from division
- MROUND: Returns a number rounded to the desired multiple
- ODD: Rounds a number up to the nearest odd integer
- POWER: Returns the result of a number raised to a power
- QUOTIENT: Returns the integer portion of a division
- RAND: Returns a random number between 0 and 1
- RANDBETWEEN: Returns a random number between the numbers you specify
- ROMAN: Converts an arabic numeral to roman, as text
- ROUND: Rounds a number to a specified number of digits
- ROUNDDOWN: Rounds a number down, toward zero
- ROUNDUP: Rounds a number up, away from zero
- RUNNINGSUM [Group]: Similar to sum, but shows the running total to a given point in the report
- SIGN: Returns the sign of a number
- SQRT: Returns a positive square root
- SUM [Group]: The sum of a sequence of numbers
Statistical functions
Note: These functions are supported on Group Formula section of the Display tab, and NOT for Computed Columns.- AVERAGE [Group]: Returns the average of its arguments
- COUNT [Group]: Counts how many numbers are in the list of arguments
- COUNTDISTINCT [Group]: The count of distinct values
- MAX [Group]: Returns the maximum value in a list of arguments
- MEDIAN [Group]: Returns the median of the given numbers
- MIN [Group]: Returns the minimum value in a list of arguments
- MODE [Group]: Returns the most common value in a data set
- STDEVP [Group: StdDev]: Calculates standard deviation based on the entire population
- VARP [Group: Variance]: Calculates variance based on the entire population
Text functions
- CHAR: Returns the character specified by the code number
- CODE: Returns a numeric code for the first character in a text string
- CONCATENATE: Joins several text items into one text item
- EXACT: Checks to see if two text values are identical
- FIND: Finds one text value within another (case-sensitive)
- LEFT: Returns the leftmost characters from a text value
- LEN: Returns the number of characters in a text string
- LOWER: Converts text to lowercase
- MID: Returns a specific number of characters from a text string starting at the position you specify
- REPLACE: Replaces characters within text
- REPT: Repeats text a given number of times
- RIGHT: Returns the rightmost characters from a text value
- SEARCH: Finds one text value within another (not case-sensitive)
- T: Converts its arguments to text
- TRIM: Removes spaces from text
- UPPER: Converts text to uppercase
- VALUE: Converts a text argument to a number