General Caster v2.2 is now available. Four new integrations to cast formula results into item name and auto-increment a Number column. Try it now!

Documentation

Sections

  1. Available functions
  2. Differences between General Caster and Formula column
  3. Using Formula column as input for your formula
  4. Useful functions when working with subitems, mirrored columns and all columns that can contain multiple values
  5. Column values retrieved and casted by General Caster
  6. Working with dates
  7. Cast result to mirrored column
  8. If X then "do" else "do nothing"
  9. Error codes
  10. Debug your formula

Available functions

General Caster uses the same syntax used in Microsoft Excel formulas.

Function Name Category Arguments
MONDAY.GENERAL.COUNT Monday 1
MONDAY.NUMBER.MIN Monday 1
MONDAY.NUMBER.MAX Monday 1
MONDAY.NUMBER.SUM Monday 1
MONDAY.NUMBER.AVG Monday 1
MONDAY.DATE.MIN Monday 1
MONDAY.DATE.MAX Monday 1
MONDAY.DATE.TIMELINE Monday 1
MONDAY.DURATION.MIN Monday 1
MONDAY.DURATION.MAX Monday 1
MONDAY.DURATION.SUM Monday 1
MONDAY.DURATION.AVG Monday 1
MONDAY.TIMELINE.SUM Monday 1
ABS Math and Trig 1
ACCRINT Financial 4-8
ACCRINTM Financial 3-5
ACOS Math and Trig 1
ACOSH Math and Trig 1
ACOT Math and Trig 1
ACOTH Math and Trig 1
ADDRESS Lookup and Reference 2-5
AMORDEGRC Financial 6,7
AMORLINC Financial 6,7
AND Logical 1+
ARABIC Math and Trig 1
ASIN Math and Trig 1
ASINH Math and Trig 1
ATAN Math and Trig 1
ATAN2 Math and Trig 2
ATANH Math and Trig 1
AVEDEV Statistical 1+
AVERAGE Statistical 1+
AVERAGEA Statistical 1+
AVERAGEIF Statistical 2,3
AVERAGEIFS Statistical 3+
BASE Math and Trig 2,3
BESSELI Engineering 2
BESSELJ Engineering 2
BESSELK Engineering 2
BESSELY Engineering 2
BETADIST Statistical 3-5
BETAINV Statistical 3-5
BETA.INV Statistical 3-5
BIN2DEC Engineering 1
BIN2HEX Engineering 1,2
BIN2OCT Engineering 1,2
BINOMDIST Statistical 4
BINOM.DIST Statistical 4
BINOM.DIST.RANGE Statistical 3,4
BINOM.INV Statistical 3
BITAND Engineering 2
BITOR Engineering 2
BITXOR Engineering 2
BITLSHIFT Engineering 2
BITRSHIFT Engineering 2
CEILING Math and Trig 1-2
CEILING.MATH Math and Trig 1-3
CEILING.PRECISE Math and Trig 1,2
CHAR Text and Data 1
CHIDIST Statistical 2
CHISQ.DIST Statistical 3
CHISQ.DIST.RT Statistical 2
CHIINV Statistical 2
CHISQ.INV Statistical 2
CHISQ.INV.RT Statistical 2
CHITEST Statistical 2
CHISQ.TEST Statistical 2
CHOOSE Lookup and Reference 2+
CLEAN Text and Data 1
CODE Text and Data 1
COLUMN Lookup and Reference -1
COLUMNS Lookup and Reference 1
COMBIN Math and Trig 2
COMBINA Math and Trig 2
COMPLEX Engineering 2,3
CONCAT Text and Data 1+
CONCATENATE Text and Data 1+
CONFIDENCE Statistical 3
CONFIDENCE.NORM Statistical 3
CONVERT Engineering 3
CORREL Statistical 2
COS Math and Trig 1
COSH Math and Trig 1
COT Math and Trig 1
COTH Math and Trig 1
COUNT Statistical 1+
COUNTA Statistical 1+
COUNTBLANK Statistical 1
COUNTIF Statistical 2
COUNTIFS Statistical 2+
COUPDAYBS Financial 3,4
COUPDAYS Financial 3,4
COUPDAYSNC Financial 3,4
COUPNCD Financial 3,4
COUPNUM Financial 3,4
COUPPCD Financial 3,4
COVAR Statistical 2
COVARIANCE.P Statistical 2
CRITBINOM Statistical 3
CSC Math and Trig 1
CSCH Math and Trig 1
CUMIPMT Financial 6
CUMPRINC Financial 6
DATE Date and Time 3
DATEDIF Date and Time 2,3
DATEVALUE Date and Time 1
DAVERAGE Database 3
DAY Date and Time 1
DAYS Date and Time 2
DAYS360 Date and Time 2,3
DB Financial 4,5
DCOUNT Database 3
DCOUNTA Database 3
DDB Financial 4,5
DEC2BIN Engineering 1,2
DEC2HEX Engineering 1,2
DEC2OCT Engineering 1,2
DEGREES Math and Trig 1
DELTA Engineering 1,2
DEVSQ Statistical 1+
DGET Database 3
DISC Financial 4,5
DMAX Database 3
DMIN Database 3
DOLLAR Text and Data 1,2
DOLLARDE Financial 2
DOLLARFR Financial 2
DPRODUCT Database 3
DSTDEV Database 3
DSTDEVP Database 3
DSUM Database 3
DVAR Database 3
DVARP Database 3
EDATE Date and Time 2
EFFECT Financial 2
ENCODEURL Web 1
EOMONTH Date and Time 2
ERF Engineering 1,2
ERF.PRECISE Engineering 1
ERFC Engineering 1
ERFC.PRECISE Engineering 1
ERROR.TYPE Information 1
EVEN Math and Trig 1
EXACT Text and Data 2
EXP Math and Trig 1
EXPONDIST Statistical 3
EXPON.DIST Statistical 3
FACT Math and Trig 1
FACTDOUBLE Math and Trig 1
FALSE Logical 0
F.DIST Statistical 4
FIND Text and Data 2,3
FINDB Text and Data 2,3
FISHER Statistical 1
FISHERINV Statistical 1
FIXED Text and Data 1-3
FLOOR Math and Trig 1-2
FLOOR.MATH Math and Trig 1-3
FLOOR.PRECISE Math and Trig 1-2
FORECAST Statistical 3
FORECAST.LINEAR Statistical 3
FORMULATEXT Lookup and Reference 1
FV Financial 3-5
FVSCHEDULE Financial 2
GAMMA Statistical 1
GAMMADIST Statistical 4
GAMMA.DIST Statistical 4
GAMMAINV Statistical 3
GAMMA.INV Statistical 3
GAMMALN Statistical 1
GAMMALN.PRECISE Statistical 1
GAUSS Statistical 1
GCD Math and Trig 1+
GEOMEAN Statistical 1+
GESTEP Engineering 1,2
GROWTH Statistical 1-4
HARMEAN Statistical 1+
HEX2BIN Engineering 1,2
HEX2DEC Engineering 1
HEX2OCT Engineering 1,2
HLOOKUP Lookup and Reference 3,4
HOUR Date and Time 1
HYPERLINK Lookup and Reference 1,2
HYPGEOMDIST Statistical 4
IF Logical 1-3
IFERROR Logical 2
IFNA Logical 2
IFS Logical 2+
IMABS Engineering 1
IMAGINARY Engineering 1
IMARGUMENT Engineering 1
IMCONJUGATE Engineering 1
IMCOS Engineering 1
IMCOSH Engineering 1
IMCOT Engineering 1
IMCSC Engineering 1
IMCSCH Engineering 1
IMDIV Engineering 2
IMEXP Engineering 1
IMLN Engineering 1
IMLOG10 Engineering 1
IMLOG2 Engineering 1
IMPOWER Engineering 2
IMPRODUCT Engineering 1+
IMREAL Engineering 1
IMSEC Engineering 1
IMSECH Engineering 1
IMSIN Engineering 1
IMSINH Engineering 1
IMSQRT Engineering 1
IMSUB Engineering 2
IMSUM Engineering 1+
IMTAN Engineering 1
INDEX Lookup and Reference 1-4
INDIRECT Lookup and Reference 1,2
INT Math and Trig 1
INTERCEPT Statistical 2
INTRATE Financial 4,5
IPMT Financial 4-6
IRR Financial 1,2
ISBLANK Information 1
ISERR Information 1
ISERROR Information 1
ISEVEN Information 1
ISFORMULA Information 1
ISLOGICAL Information 1
ISNA Information 1
ISNONTEXT Information 1
ISNUMBER Information 1
ISODD Information 1
ISOWEEKNUM Date and Time 1
ISPMT Financial 4
ISTEXT Information 1
KURT Statistical 1+
LARGE Statistical 2
LCM Math and Trig 1+
LEFT Text and Data 1,2
LEFTB Text and Data 1,2
LEN Text and Data 1
LENB Text and Data 1
LINEST Statistical 1-4
LN Math and Trig 1
LOG Math and Trig 1,2
LOG10 Math and Trig 1
LOGEST Statistical 1-4
LOGINV Statistical 3
LOGNORMDIST Statistical 3
LOGNORM.DIST Statistical 4
LOGNORM.INV Statistical 3
LOOKUP Lookup and Reference 2,3
LOWER Text and Data 1
MATCH Lookup and Reference 2,3
MAX Statistical 1+
MAXA Statistical 1+
MAXIFS Statistical 3+
MDETERM Math and Trig 1
MEDIAN Statistical 1+
MID Text and Data 3
MIDB Text and Data 3
MIN Statistical 1+
MINA Statistical 1+
MINIFS Statistical 3+
MINUTE Date and Time 1
MINVERSE Math and Trig 1
MIRR Financial 3
MMULT Math and Trig 2
MOD Math and Trig 2
MODE Statistical 1+
MODE.SNGL Statistical 1+
MONTH Date and Time 1
MROUND Math and Trig 2
MULTINOMIAL Math and Trig 1+
MUNIT Math and Trig 1
N Information 1
NA Information 0
NEGBINOMDIST Statistical 3
NETWORKDAYS Date and Time 2-3
NOMINAL Financial 2
NORMDIST Statistical 4
NORM.DIST Statistical 4
NORMINV Statistical 3
NORM.INV Statistical 3
NORMSDIST Statistical 1
NORM.S.DIST Statistical 1,2
NORMSINV Statistical 1
NORM.S.INV Statistical 1
NOT Logical 1
NOW Date and Time 0
NPER Financial 3-5
NPV Financial 2+
NUMBERVALUE Text and Data 1+
OCT2BIN Engineering 1,2
OCT2DEC Engineering 1
OCT2HEX Engineering 1,2
ODD Math and Trig 1
OFFSET Lookup and Reference 3-5
OR Logical 1+
PDURATION Financial 3
PEARSON Statistical 2
PERCENTILE Statistical 2
PERCENTILE.INC Statistical 2
PERCENTRANK Statistical 2,3
PERCENTRANK.INC Statistical 2,3
PERMUT Statistical 2
PERMUTATIONA Statistical 2
PI Math and Trig 0
PMT Financial 3-5
POISSON Statistical 3
POISSON.DIST Statistical 3
POWER Math and Trig 2
PPMT Financial 4-6
PRICE Financial 6,7
PRICEDISC Financial 4,5
PRICEMAT Financial 5,6
PRODUCT Math and Trig 1+
PROPER Text and Data 1
PV Financial 3-5
QUARTILE Statistical 2
QUARTILE.INC Statistical 2
QUOTIENT Math and Trig 2
RADIANS Math and Trig 1
RAND Math and Trig 0
RANDBETWEEN Math and Trig 2
RANK Statistical 2,3
RANK.EQ Statistical 2,3
RATE Financial 3-6
RECEIVED Financial 4-5
REPLACE Text and Data 4
REPLACEB Text and Data 4
REPT Text and Data 2
RIGHT Text and Data 1,2
RIGHTB Text and Data 1,2
ROMAN Math and Trig 1,2
ROUND Math and Trig 2
ROUNDDOWN Math and Trig 2
ROUNDUP Math and Trig 2
ROW Lookup and Reference -1
ROWS Lookup and Reference 1
RRI Financial 3
RSQ Statistical 2
SEARCH Text and Data 2,3
SEARCHB Text and Data 2,3
SEC Math and Trig 1
SECH Math and Trig 1
SECOND Date and Time 1
SERIESSUM Math and Trig 4
SIGN Math and Trig 1
SIN Math and Trig 1
SINH Math and Trig 1
SKEW Statistical 1+
SLN Financial 3
SLOPE Statistical 2
SMALL Statistical 2
SQRT Math and Trig 1
SQRTPI Math and Trig 1
STANDARDIZE Statistical 3
STDEV Statistical 1+
STDEV.S Statistical 1+
STDEV.P Statistical 1+
STDEVA Statistical 1+
STDEVP Statistical 1+
STDEVPA Statistical 1+
STEYX Statistical 2
SUBSTITUTE Text and Data 3,4
SUBTOTAL Math and Trig 2+
SUM Math and Trig 1+
SUMIF Math and Trig 2,3
SUMIFS Math and Trig 3+
SUMPRODUCT Math and Trig 1+
SUMSQ Math and Trig 1+
SUMX2MY2 Math and Trig 2
SUMX2PY2 Math and Trig 2
SUMXMY2 Math and Trig 2
SWITCH Logical 3+
SYD Financial 4
T Text and Data 1
TAN Math and Trig 1
TANH Math and Trig 1
TBILLEQ Financial 3
TBILLPRICE Financial 3
TBILLYIELD Financial 3
TDIST Statistical 3
TEXT Text and Data 2
TEXTJOIN Text and Data 3+
TIME Date and Time 3
TIMEVALUE Date and Time 1
TINV Statistical 2
T.INV Statistical 2
TODAY Date and Time 0
TRANSPOSE Lookup and Reference 1
TREND Statistical 1-4
TRIM Text and Data 1
TRIMMEAN Statistical 2
TRUE Logical 0
TRUNC Math and Trig 1,2
TYPE Information 1
UNICHAR Text and Data 1
UNICODE Text and Data 1
UPPER Text and Data 1
USDOLLAR Financial 2
VALUE Text and Data 1
VAR Statistical 1+
VAR.P Statistical 1+
VAR.S Statistical 1+
VARA Statistical 1+
VARP Statistical 1+
VARPA Statistical 1+
VLOOKUP Lookup and Reference 3,4
WEBSERVICE Web 1
WEEKDAY Date and Time 1,2
WEEKNUM Date and Time 1,2
WEIBULL Statistical 4
WEIBULL.DIST Statistical 4
WORKDAY Date and Time 2-3
XIRR Financial 2,3
XNPV Financial 3
XOR Logical 1+
YEAR Date and Time 1
YEARFRAC Date and Time 2,3
YIELDDISC Financial 4,5
YIELDMAT Financial 5,6
ZTEST Statistical 2-3
Z.TEST Statistical 2-3

Differences between General Caster and Formula column

Although Formula column syntax is similar to General Caster syntax, there are some differences.

If you are converting all your formulas from Formula column to General Caster, you can't simply copy the formula from the column and paste it into the formula composer in General Caster.

In the Formula column, when you click on a column reference, {Column} is added to the text. General Caster uses a slightly different reference, for example {item's Column}. Just click on the corresponding column button at the bottom of formula composer in General Caster to get the correct column reference.

Keep in mind that, due to how monday.com works, you can have multiple columns with the same name. To prevent misleading references, make sure the columns you use in the formula have a unique name.

In addition, there are the following differences.

Formula function Corresponding General Caster function
ADD_DAYS Not available. Use TEXT(DATEVALUE({item's Date}) + 5, "YYYY-MM-DD")
DIVIDE Not available. Use {item's Number 1} / {item's Number 2}
FORMAT_DATE TEXT
HOURS_DIFF HOUR
MINUS Not available. Use {item's Number 1} - {item's Number 2}
MULTIPLY Not available. Use {item's Number 1} * {item's Number 2}
SUBTRACT_DAYS Not available. Use TEXT(DATEVALUE({item's Date}) - 5, "YYYY-MM-DD")
WORKDAYS WORKDAY
{Timeline#Start} LEFT({item's Timeline}, 10)
{Timeline#End} RIGHT({item's Timeline}, 10)
{Time Tracking#Hours} HOUR({item's Time Tracking})
{Time Tracking#Minutes} MINUTE({item's Time Tracking})
{Time Tracking#Seconds} SECOND({item's Time Tracking})

Using Formula column as input for your formula

Native monday.com's Formula column doesn't actually contain what you see as value in the column. The result is calculated on client side so it can't be used as input for your formula in General Caster (and in all other workflows and chart).

If you want to use the result of a native Formula column in General Caster, you need to integrate it into your General Caster's formula, following the instruction you find in the section "Differences between General Caster and Formula column".

You can also chain multiple General Caster integrations casting on different column. For example:

  1. When Date changes, perform formula and cast result to Number;
  2. When Number changes, perform formula and cast result to Status.

Useful functions when working with subitems, mirrored columns and all columns that can contain multiple values

General Caster supports subitems, mirrored summary columns and other columns that can contain multiple values.

For these columns, General Caster provides helper functions to retrieve a single value, for example the lowest number in the group or the latest date, or to perform basic math calculations, like getting the sum of multiple numbers or calculate the average, before using the result in your formula.

Helper functions require a M plan or higher.

Function Useful for Column Description
MONDAY.GENERAL.COUNT Any Returns the number of items in the column
MONDAY.NUMBER.MIN Number Returns the lowest number
MONDAY.NUMBER.MAX Number Returns the highest number
MONDAY.NUMBER.SUM Number Returns the sum of all the numbers
MONDAY.NUMBER.AVG Number Returns the average (arithmetic mean) of all the numbers
MONDAY.DATE.MIN Date and Timeline Returns the earliest date in YYYY-MM-DD format
MONDAY.DATE.MAX Date and Timeline Returns the latest date in YYYY-MM-DD format
MONDAY.DATE.TIMELINE Date and Timeline Returns a timeline from earliest to latest date in YYYY-MM-DD+YYYY-MM-DD format
MONDAY.DURATION.MIN Time Tracking Returns the shortest period in HH:MM:SS format
MONDAY.DURATION.MAX Time Tracking Returns the longest period in HH:MM:SS format
MONDAY.DURATION.SUM Time Tracking Returns the sum of all the periods in HH:MM:SS format
MONDAY.DURATION.AVG Time Tracking Returns the average period in HH:MM:SS format
MONDAY.TIMELINE.SUM Timeline Returns the sum of days contained in all timelines

Existing helper functions are now deprecated. They keep working but will soon removed. Please update all your formulas containing the following functions:

HELPER.COUNT HELPER.MIN HELPER.MAX HELPER.SUM HELPER.AVG HELPER.EARLIEST HELPER.LATEST HELPER.EARLIESTLATEST

Column values retrieved and casted by General Caster

You can treat most column and values retrieved and casted by General Caster as simple text or numbers. Columns with multiple values, like dropdowns, are returned as first_value+second_value+third_value. Columns with "rich" values, like locations, are returned as first_part:second_part:third_part.

Column Value Example
Item Name Name of the item
Paid plan required
"Item 1"
Item Group Group of the item
Paid plan required
"Group Title"
Checkbox Boolean value true if column is checked, false otherwise
Country ISO 2-letter Country code "IT" or "US"
Date ISO 8601 date/time "2013-02-08" or "2013-02-08 17:30:00" (time must be in UTC)
Dropdown One or more labels separated by plus "Red" or "Red+White+Green"
Email Email address and displayed name separated by colon "email@company.com:Company Inc."
File (readonly) One or more file names separated by plus
Paid plan required
"document.doc" or "document.doc+data.xls"
Hour ISO 8601 time "17:30"
Jira (readonly) Link to the corresponding item in Jira
L plan required
"https://monday.atlassian.net/browse/TBT-1"
Link Web address and displayed text separated by colon. Escape : occurences in the URL with \. "https://monday.omnidea.it:Monday" or "https://monday.omnidea.it?key\:value:Monday link including \:"
Location Latitude, longitude and displayed text separated by colon "45.438611:12.326667:Venice"
Long Text Simple text "Lorem ipsum dolor sit amet"
Mirror One or more values separated by plus
Paid plan required
"69420" if only one item is linked or "69420+42069" if two items are linked
Number Simple numbers 420
People ID:type:name separated by plus
Paid plan required
"69420:person:Tim Apple+42069:team:Management"
Phone Phone number and ISO 2-letter Country code separated by colon "42069420:US"
Rating A number between 1 and the rating scale defined in the column 5
Status Status label "Done"
Tags One or more tag IDs separated by plus "69420" or "69420+42069"
Team Team ID "69420"
Text Simple text "Lorem ipsum dolor sit amet"
Time Tracking (readonly) Time passed in HH:MM:SS format 01:23:45 for 1 hour, 23 mins and 45 secs. See available functions to extract hours, minutes and seconds.
Timeline Two ISO 8601 dates separated by plus "2013-02-08+2013-02-20"
Week Two ISO 8601 dates separated by plus starting at the beginning of the week defined in your account and ending 7 days later "2013-02-08+2013-02-14"
World Clock ISO timezone "Europe/Rome"

Working with dates

A date value retrieved by General Caster from a Date column is a string in the YYYY-MM-DD format, for example a simple formula like {item's Date} returns 2013-02-08.

If you need to use it without any date calculations, like combining two dates from two different Date columns to create a Timeline, just use the CONCATENATE function to get the correct Timeline format described above: CONCATENATE({item's From}, "+", {item's To}).

If you need to perform calculations with your date before casting the result, like adding or subtracting days, extracting the weekday and so forth, you need to convert the date from a string in YYYY-MM-DD format to a valid date value using the DATEVALUE function.

So, the formula DATEVALUE({item's Date}) + 5 will return a new date that is 5 days after the one set in Date column.

This result is still not ready to be casted into a destination Date column, because monday.com requires a string in the YYYY-MM-DD format, as explained above.

In order to achieve that, use the TEXT function to convert it back into the correct format. For example the formula TEXT(DATEVALUE({item's Date}) + 5, "YYYY-MM-DD") takes a date from the Date column, converts it into a date value, adds 5 days and converts it back to a string ready to be casted.

Visit the Ideas page, containing some popular Date formulas, to get inspiration for your next formula.

If you cast a wrong string (not in the YYYY-MM-DD format), the destination Date column could show a weird date, like many years in the past or in the future. Follow instructions in the Debug section to check if your formula result is correct.

Cast result to mirrored column

As monday.com doesn't display mirror column in columns dropdown, use the dedicated "cast result to mirrored column" integration, that requires you to type the ID of the mirrored column.

Column Id

Column ID is displayed at the top of the menu that appears when you click on the green arrow beside column title.

If you don't see it, enable Column IDs displaying by following the steps below:

  1. Click on your Profile Icon on bottom left;
  2. Click on monday.labs menu;
  3. Activate Developer mode.

"Cast result to mirrored column" integration requires a paid plan.

Limitation: The integration doesn't work if multiple boards are connected in the corresponding "Connected Boards" column.

If X then "do" else "do nothing"

Sometimes you want to leave a column value unchanged until a condition is verified.

In this case, use "DO-NOTHING" as result of your formula.

For example, using IF({item's Status} = "Done", "Completed", "DO-NOTHING") formula, the destination column will be updated with "Completed" value only when Status will be set to "Done".

Error codes

If a formula cannot correctly evaluate a result, General Caster displays an error value, such as #DIV/0!, #NAME?, #NUM! and #VALUE!. Each error type has different causes, and different solutions.

Error Meaning Solution
#DIV/0! General Caster displays this error when a number is divided either by zero (0) or by a cell that contains no value. Add an error handler like IFERROR.
#NAME? This error is displayed when General Caster does not recognize text in a formula. For example, a range name or the name of a function may be spelled incorrectly. If you’re using a function, make sure the function name is spelled correctly.
#NUM! General Caster displays this error when a formula or function contains invalid numeric values. Check the value is a valid number.
#VALUE! General Caster can display this error if your formula includes cells that contain different data types. Convert data type before performing calculations. For example, use DATEVALUE to convert a string to a date.

Debug your formula

Before casting formula result into destination columns, temporary cast it into a Text column to make sure the value is in the right format.

More than 36,742,000 operations performed by over 2,700 users. And counting...
General Caster
General Caster is a monday.com app
© 2024 Omnidea S.r.l.
System Status ...