The monday.com Hidden Functions

The monday formula column can be very versatile providing a mechanism to augment data visibility, add complex sorting and filtering capabilities and more. I was doing research for the videos about the formula column when I found a long list of mostly undocumented functions. This page is intended to help those who would like to be able to utilize some of these functions.

Some might ask why monday doesn't include these. I don't know for sure. But, including a list of over 300 different functions could arguably make using them MORE complicated and intimidating to the casual user. Whatever the reason, those who want more now have a place to go.

I will continue adding details on more of the functions along the way.

To see monday's documented functions go here: List of all available formulas

Array Functions 

ARGS2ARRAY
Combines arguments into an array. Arguments can be mixed types: logical, numeric, string, date, array.
Example: ARGS2ARRAY("2021/12/24", "2021/12/31")

SPLIT
Splits a string into an array, as specified by delimiter.
Example: SPLIT("2021/12/24, 2021/12/31", ",")

LEN
Returns number of elements in an array.
Example: LEN(SPLIT("2021/12/24,2021/12/25,2021/12/31", ",")) => 3

Text functions 

CHAR
Returns the ASCII character represented by the given number from the extended (8 bit) ASCII codes.
Example: CHAR(123) => "{"

CODE
Returns the ASCII code for the first character in the given string from the extended (8 bit) ASCII codes.
Example: CODE("ABC") => 65

FIND
Performs a case-sensitive search for one string inside another string returning the starting position where the first string is found; returns 0 if not found.
Syntax: FIND(text_to_find, text_to search [,starting_from_position; default = 1])
Example: FIND("A", "ABCABC", 2) => 4

PROPER
Returns a given string with the first letter in each word in upper case.
Example: PROPER("john jay DOE") => "John Jay Doe"

ROMAN
Returns a given number in Roman numerals. (Although technically incorrect for numbers greater than 3999. Only works for integers. Number can be in the form of a string.)
Example: ROMAN(2021) => "MMXXI"

Regular Expressions:
From Wikipedia — "A regular expression is a sequence of characters that specifies a search pattern. Usually such patterns are used by string-searching algorithms for "find" or "find and replace" operations on strings, or for input validation".

Some notes:

• The best place I have found to figure out / test your regular expressions before using them is here: https://regex101.com.
• I have not found a way to include flags yet. Please let me know if you find out.
• Squiggle brackets "{}" are commonly used in regular expressions. However, they can not be used directly in monday formulas they denote column references. If you HAVE to have them, you can use them by putting a string in a text or long text column and reference the column OR use CHAR(123) and CHAR(125) and construct your expression string inline.

REGEXEXTRACT
Returns the first matching substring for a given string using regular expressions.
Syntax: REGEXEXTRACT(source_text, regular_expresstion_text)
Example: REGEXEXTRACT("I like monday and I like pizza.", "like (\w+)") => "monday"

REGEXMATCH
Tests a given string for a match using regular expressions. Returns TRUE or FALSE value.
Syntax: REGEXMATCH(source_text, regular_expresstion_text)
Example: REGEXMATCH ("1234567890", ".*456.*") => TRUE

REGEXREPLACE
Replaces all or part of a string using regular expressions.
Syntax: REGEXREPLACE(source_text, regular_expresstion_text, replacement_text)
Example 1: REGEXREPLACE("1234567890", "(…)(…)(….)", "($1) $2-$3") => "(123) 456-7890"
Example 2: REGEXREPLACE("1234567890", "456", "") => "1237890"

Quick Tip – Notice in Example 2 above, you can use REGEXREPLACE() to replace portions of a string will a null string.

Previously, the SUBSTITUTE() function was not able to do the same. For example, this formula, a common way to count words, did not work:

IF({Text}, LEN(TRIM({Text})) – LEN(SUBSTITUTE(TRIM({Text}), " ", "")) + 1, 0)

This is no longer true. You can now use REGEXREPLACE() OR SUBSTITUTE() to remove text from a string! The formula above now works!!

SUBSTITUTE
This function is documented in monday without the final, optional "occurrence" parameter.
Replaces text in a given string. When "occurrence" is given, will replace only that occurrence of the find_text, otherwise, all occurrences of the find_text will be replaced.
Syntax: SUBSTITUTE(text, find_text, replace_text [,occurrence])
Example: SUBSTITUTE("I like pizza and pizza likes me.", "pizza", "apples", 1) => "I like apples and pizza likes me."

Logical functions

CHOOSE
Returns the nth parameter from a list, returns an error when n does not map to a given parameter.
Syntax: CHOOSE(n, "val1" [,"val2"]…)
Example: CHOOSE(WEEKDAY(TODAY()), "sun", "mon", "tue", "wed", "thu", "fri", "sat") => "mon" (in case where today is Monday)

EQ
Compares two numeric values, returns true if the first is equal to the second and false if not.
Example: EQ(1,2) =>FALSE

GTE
Compares two numeric values, returns true if the first is greater than or equal to the second and false if not.
Example: GTE(1,2) =>FALSE

LT
Compares two numeric values, returns true if the first is less than the second and false if not. (Note: there is no corresponding GT function)
Example: LT(1,2) =>TRUE

LTE
Compares two numeric values, returns true if the first is less than or equal to the second and false if not.
Example: LTE(1, 2) =>TRUE

NOT
Returns the logical opposite of the argument.
Example: NOT(1=2) => True

Numeric functions

EVEN
Returns the even integer greater than or equal to a given number when the given number is greater than zero. Returns the even integer less than or equal to a given number when the given number is less than or equal to zero.
Example: EVEN(3) => 4

INT
Returns the integer less than or equal to a given number
Examples: INT(1.9) = 1; INT(-1.1) = -2

ODD
Returns the odd integer greater than or equal to a given number when the given number is greater than zero. Returns the odd integer less than or equal to a given number when the given number is less than or equal to zero.
Example: ODD(0) => -1

PRODUCT
Returns the product of all arguments.
Example: PRODUCT(2, 3, 4) => 24

SIGN
Returns -1, 0 or 1 based on the sign of the given number.
Example: SIGN(-5) => -1

Date and Time Functions

DATEVALUE
Returns a serial number representing the date given in the form of a string. The value is the same value returned by Excel and Google Sheets.
Example: DATEVALUE("2021/11/15") => 44516

IMPORTANT!!! – When dealing with column values or NOW/TODAY, use an inner FORMAT_DATE() to eliminate time zone issues.

Example: DATEVALUE(FORMAT_DATE({Date}))

Quick Tip – For dates AFTER February 28, 1900, you can convert back to a date by using the ADD_DAYS() function from December 31, 1899.

Example: ADD_DAYS("1899-12-31", DATEVALUE(FORMAT_DATE({Date})) – 1.0001)
(Note: the ".0001" is to deal with internal calculation issues.)

EDATE
Adds (or subtracts) months to the given date. Returns a serial number representing the calculated date.
Example: FORMAT_DATE(EDATE("2019-01-20", 1)) => "Feb 20, 2019"

EOMONTH
Adds months to the last day of the month of given date. Returns a serial number representing the calculated date. 
Example: FORMAT_DATE(EOMONTH("2021/11/15", 0)) => "Nov 30, 2021" 

FORMAT_DATE
Returns a formatted date/time as determined by the codes below. If the optional formatting string is omitted, the default "MMM DD, YYYY" will be used. Square brackets, "[", and "]" can be used to specify constants, e.g., FORMAT_DATE(NOW(), "dddd [the] Do [of] MMMM")
Syntax: FORMAT_DATE(date [, formatting string in quotes])
Example 1: FORMAT_DATE(NOW()) => "Jun 13, 2022" 
Example 2: FORMAT_DATE(NOW(), "MM/DD/YYYY") => "06/13/2022"
Example 3: FORMAT_DATE(NOW(), "dddd [the] Do [of] MMMM") => "Monday the 12th of June"

NETWORKDAYS
Returns the number of work days between two dates (inclusive, excluding Saturday and Sunday, and optionally, holidays).
Syntax: NETWORKDAYS(start_date, end_date [,holidays as array])
Example: NETWORKDAYS("2021/11/26", "2021/12/02", ARGS2ARRAY("2021/11/30", "2021/12/1")) => 3

NOW
Returns the current date and time. Same as TODAY().
Example: FORMAT_DATE(NOW(), "Y-M-D hh:mm:ss") => "2021-11-30 02:39:27"

TIMEVALUE
Returns a number representing the time as a fraction of a day of a date and time given in the form of a string. The date portion is required but not significant. The value is the same value returned by Excel and Google Sheets.
Example: TIMEVALUE(NOW()) => 0.375 (at 9:00 AM)

Quick Tip #1 – To get a date+time value that you can do math with (like in Excel/Google Sheets): combine DATEVALUE and TIMEVALUE like this:

DATEVALUE(FORMAT_DATE({Date})) + TIMEVALUE({Date})

… the inner FORMAT_DATE will eliminate time zone issues.

Quick Tip #2 – To use TIMEVALUE with HOURS columns, add a date to the beginning of the string.

Example: TIMEVALUE("1/1/1 " & {Hour}) => 0.458 when {Hour} = "11:00 AM"

WEEKDAY
Returns the day of the week number of a given date, with Monday = 1, Sunday = 7.
Example: WEEKDAY("2021/04/06") => 3

WORKDAY
Adds workdays (excludes Saturday and Sunday, and optionally, holidays) to a date and returns the new date. Similar to the ADD_DAYS function.
Syntax: WORKDAY(date, days_to_add [,holidays as array])
Example: WORKDAY("2021/12/1", 21, ARGS2ARRAY("2021/12/24", "2021/12/31")) => "Jan 03, 2022"

YEARFRAC
Returns the time between two dates in years.
Example: YEARFRAC("2021/1/1", "2022/8/12") => 1.614

Financial Functions 

PMT
Calculates the periodic payment amount for a loan or annuity.
Syntax: PMT(rate, number_of_periods, present_value [,future_value, payment_timing])
     rate – percentage per period
     future_value – OPTIONAL, default = 0
     payment_timing – OPTIONAL, default = 0, 0 = payment at beginning of period, 1 = payment at end of period
Example: PMT(5%/12, 30*12, 100000) => -536.82

Statistical Functions 

RAND
Returns a pseudorandom number greater than or equal to 0 and less than 1. A reference value can be used to force RAND() to be reevaluated each item or they will return the same value for every item on the board. One simple way to do this is to use an Item ID column as reference in the functions.
Syntax: RAND([reference])
     reference – OPTIONAL, will return the same pseudorandom number for each unique reference when board functions are recalculated.
Example 1: RAND() => 0.09375362836816792 (Same value returned for every item on the board)
Example 2: RAND({Item ID}) => 0.23092307070815754 (where {Item ID} is an Item ID column. A different value will be returned for every item on the board)

RANDBETWEEN
Returns a pseudorandom integer between two values, inclusive. By including a column reference that has a unique value for each item, the function can be forced to be reevaluated for each item. Otherwise, it will return the same value for every item on the board. One simple way to do this is to use an Item ID column as reference in the functions.
Syntax: RANDBETWEEN({Lower Bound}, {Upper Bound})
     Returns a pseudorandom integer greater than or equal to {Lower Bound} and less than or equal to {Upper Bound}.
Example 1: RANDBETWEEN(1, 10) => 8 (Same value returned for every item on the board)
Example 2: RANDBETWEEN(1, 10 + 0 * {Item ID}) => 3 (where {Item ID} is an Item ID column. A different value will be returned for every item on the board)

The Rest (feel free to send me details and I'll add them)

^{ACCRINT, ACOS, ACOSH, ACOT, ACOTH, ADD, AGGREGATE, ARABIC, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGEA, AVERAGEIF, AVERAGEIFS, BASE, BESSELI, BESSELJ, BESSELK, BESSELY, BETA.DIST, BETA.INV, BETADIST, BETAINV, BIN2DEC, BIN2HEX, BIN2OCT, BINOM.DIST, BINOM.DIST.RANGE, BINOM.INV, BINOMDIST, BITAND, BITLSHIFT, BITOR, BITRSHIFT, BITXOR, CEILING, CEILINGMATH, CEILINGPRECISE, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CLEAN, COLUMN, COLUMNS, COMBIN, COMBINA, COMPLEX, CONFIDENCE, CONFIDENCE.NORM, CONFIDENCE.T, CONVERT, CORREL, COS, COSH, COT, COTH, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COUNTIN, COUNTUNIQUE, COVARIANCE.P, COVARIANCE.S, CSC, CSCH, CUMIPMT, CUMPRINC, DAYS360, DB, DDB, DEC2BIN, DEC2HEX, DEC2OCT, DECIMAL, DEGREES, DELTA, DEVSQ, DOLLARDE, DOLLARFR, E, EFFECT, ERF, ERFC, EXP, EXPON.DIST, EXPONDIST, F.DIST, F.DIST.RT, F.INV, F.INV.RT, FACT, FACTDOUBLE, FDIST, FDISTRT, FINV, FINVRT, FISHER, FISHERINV, FLATTEN, FLOOR, FORECAST, FREQUENCY, FV, FVSCHEDULE, GAMMA, GAMMA.DIST, GAMMA.INV, GAMMADIST, GAMMAINV, GAMMALN, GAMMALN.PRECISE, GAUSS, GCD, GEOMEAN, GESTEP, GROWTH, HARMEAN, HEX2BIN, HEX2DEC, HEX2OCT, HTML2TEXT, HYPGEOM.DIST, HYPGEOMDIST, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, INTERCEPT, INTERVAL, IPMT, IRR, ISBINARY, ISBLANK, ISEVEN, ISLOGICAL, ISNONTEXT, ISNUMBER, ISODD, ISPMT, ISTEXT, JOIN, KURT, LARGE, LCM, LINEST, LN, LOG10, LOGEST, LOGNORM.DIST, LOGNORM.INV, LOGNORMDIST, LOGNORMINV, MATCH, MAXA, MEDIAN, MINA, MIRR, MODE.MULT, MODE.SNGL, MODEMULT, MODESNGL, MROUND, MULTINOMIAL, NEGBINOM.DIST, NEGBINOMDIST, NOMINAL, NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, NORMDIST, NORMINV, NORMSDIST, NORMSINV, NPER, NPV, NUMBERS, OCT2BIN, OCT2DEC, OCT2HEX, PDURATION, PEARSON, PERCENTILEEXC, PERCENTILEINC, PERCENTRANKEXC, PERCENTRANKINC, PERMUT, PERMUTATIONA, PHI, POISSON.DIST, POISSONDIST, POW, PPMT, PROB, PV, QUARTILE.EXC, QUARTILE.INC, QUARTILEEXC, QUARTILEINC, QUOTIENT, RADIANS, RANK.AVG, RANK.EQ, RANKAVG, RANKEQ, RATE, REFERENCE, ROW, ROWS, RRI, RSQ, SEC, SECH, SERIESSUM, SIN, SINH, SKEW, SKEW.P, SKEWP, SLN, SLOPE, SMALL, SQRTPI, STANDARDIZE, STDEV.P, STDEV.S, STDEVA, STDEVP, STDEVPA, STDEVS, STEYX, SUBTOTAL, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, SYD, T, T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, TAN, TANH, TBILLEQ, TBILLPRICE, TBILLYIELD, TDIST, TDIST2T, TDISTRT, TIME, TINV, TINV2T, TRANSPOSE, TREND, TRIMMEAN, TRUNC, UNICHAR, UNICODE, UNIQUE, VAR.P, VAR.S, VARA, VARP, VARPA, VARS, WEIBULL.DIST, WEIBULLDIST, XIRR, XNPV}