Part of the monday CRM guide: pricing, setup, integrations, migration and comparisons in one place.
Free tool
monday.com formula builder and checker
Pick what you want the Formula column to calculate, type your column names, and copy a formula written in monday.com's documented syntax. If monday shows "Illegal formula", paste it into the checker below: it finds curly quotes, missing zeros and brackets, Excel-only functions and unsafe blanks, and hands back a fixed version.
Your formula
ROUND(DAYS({Due Date},TODAY()),0)Days left until the due date. A negative number means the item is overdue.
In monday.com: click any cell in the Formula column, paste into the editor, then click Set formula.
Check a formula that says "Illegal formula"
Paste the formula exactly as you have it. The checker reads it the way monday.com's rules describe, fixes what can be fixed safely and explains the rest.
Fixed version
Common monday.com formulas, ready to copy
Each one is also a formula type in the builder, where you can swap in your own column names. Column names here are examples.
| What it does | Formula |
|---|---|
| Days until a due date (negative means overdue) | ROUND(DAYS({Due Date},TODAY()),0) |
| Working days between two dates | WORKDAYS({To},{From}) |
| Length of a Timeline in working days | WORKDAYS({Timeline#End},{Timeline#Start}) |
| Add 15 days to a date, formatted | FORMAT_DATE(ADD_DAYS({Start Date},15)) |
| Text when a column is empty | IF(LEN({Email})=0,"Missing email",{Email}) |
| Status is not Done | IF({Status}="Done","Closed","Still open") |
| Labels to numbers | SWITCH({Priority},"High",3,"Medium",2,"Low",1,0) |
| Two conditions | IF(AND({Total Sales}>350000,{Deals}>12),250,0) |
| Weighted pipeline value | ROUND(MULTIPLY({Deal Value},DIVIDE({Probability},100)),2) |
| Stale deal flag | IF(DAYS(TODAY(),{Last Updated})>14,"Stale","Active") |
| Percent change | ROUND(MULTIPLY(DIVIDE(MINUS({February Sales},{January Sales}),{January Sales}),100),2) |
| Full name | CONCATENATE({First Name}," ",{Last Name}) |
| Currency format | TEXT({Amount},"$#,##0.00") |
| Day of the week | TEXT({Date},"dddd") |
Why does monday.com say "Illegal formula"?
Because something in the formula breaks one of the column's syntax rules. These are the causes monday.com's own articles describe, plus the Excel habits that produce the same error:
| What you see or did | Cause | Fix |
|---|---|---|
| Curly quotes from Word, Google Docs or Slack: “Done” | monday reads them as illegal characters | Retype them as straight quotes: "Done". The checker does this for you. |
| A decimal with no zero: MULTIPLY(10,.25) | monday requires a 0 before the decimal point | Write 0.25. The checker adds the zero. |
| A red bracket in the editor | An extra or missing parenthesis or brace | Every ( needs a ) and every { needs a }. The checker shows which one is unmatched. |
| Column name typed without braces, or with the wrong case | Names must be wrapped in {} and match the board exactly; monday treats values as case sensitive | Copy the name from the column header: {Estimated SP}, not {estimated sp}. |
| IFERROR, VLOOKUP, SUMIF or another Excel function | Not in monday's function library | Use the replacements the checker suggests (an IF with LEN for blanks; a Mirror or Connect Boards column instead of a lookup). |
| The formula works on most rows but errors where a cell is empty | The function gets a blank instead of a number or date | Check for the blank first: IF(LEN({Column})=0, "", your formula). |
| Formula pasted from Excel starting with = | monday formulas don't start with an equals sign (none of monday's examples do) | Remove the leading =. The checker does this. |
| Semicolons between arguments: IF(A;B;C) | A European Excel habit; monday's syntax uses commas | Replace ; with , outside of quoted text. The checker does this. |
If the checker finds nothing and monday still rejects the formula, the problem is usually a name: a column renamed since you wrote the formula, or a status label in quotes that is spelled differently on the board. "NaN" in a cell means the formula tried to calculate text or an empty value, per monday.com's FAQ.
How do I check if a cell is blank in a monday.com formula?
Test its length. monday.com says IFERROR isn't supported and recommends an IF check with LEN to handle empty values before the rest of the calculation runs:
IF(LEN({Start Date})=0,"",DAYS({Due Date},{Start Date}))Tick "Show a blank instead of an error" in the builder and it wraps every formula this way, checking each column the formula reads.
How do I write "not equal" in a monday.com formula?
Swap the two results of the IF. monday.com's documentation shows equals, greater than and less than comparisons, so instead of testing "status is not Done", test "status is Done" and put your answer in the false branch:
IF({Status}="Done","","Still open")To compare two columns, EXACT() returns true when they match: IF(EXACT({Owner},{Approver}),"Same person","Different"). The != operator from programming languages is not in monday's documentation; the checker flags it.
What a monday.com formula can't do
- It works across one item only. A formula reads the other cells of the same item. It can't add up a whole column or read another item, so use the column summary for totals. monday.com: "Item A cannot pull any information into a Formula Column on Item B."
- It can't read other boards. No VLOOKUP. Connect the boards with a Connect Boards column and bring the value over with a Mirror column instead.
- It can't trigger automations or fill other columns. The result is read-only. monday.com's workaround: a workflow copies the result into a regular Numbers or Text column, and the automation, email, chart or dashboard reads that column.
- It can't set a status. People search for "set status with formula", but the formula only displays a value. Copy the result into a column with a workflow, then use an automation on that column to change the status.
- TEXT() turns numbers into text. Formatting a number as currency makes the column summary show N/A instead of a total.
- 10,000 characters is the length limit in the formula builder.
Which columns can a formula read?
monday.com's two formula articles list slightly different columns, so check this before you build on a Mirror or World Clock column:
| Column | "The Formula Column" article | "Available functions" article |
|---|---|---|
| Check, Country, Creation Log, Date, Dependency, Dropdown, Email, Formula, Hour, Item ID, Last Updated, Long Text, Numbers, Person, Phone, Rating, Status, Text, Timeline, Time Tracking, Vote | Supported | Supported |
| Mirror | Supported | Listed as incompatible |
| Connect Boards / Link to Item | Connect Boards supported | Link to Item supported |
| World Clock | Supported | Listed as both supported and incompatible |
| Subitem names and count of subitems | Supported | Supported |
| Autonumber, Color Picker, Files, Link, Location, Progress Tracking, Tags, Week | Not supported | Not supported |
monday.com's Formula Use Cases article shows SUM({Mirror}) working when the mirrored column is a Numbers column, which matches the first article. Test a Mirror formula on a copy of your board before you rely on it.
How do I track how long a deal has been in one stage?
Stamp the date when the stage changes, then count from it. The Last Updated column changes on any edit, so it measures inactivity, not time in a stage. Add a Date column called Stage Date, create the automation "When status changes to something, set date to today", and use:
IF(DAYS(TODAY(),{Stage Date})>14,"Stuck","Moving")Pick "Stale deal or item age" in the builder to set your own threshold. For the rest of the CRM board design, see monday.com custom fields and formulas, explained, which works through a weighted-pipeline board step by step, and monday CRM automation recipes.
monday.com formula functions: the full list
All 56 functions in monday.com's formula library, with monday's own examples. monday.com's use-case examples also use NOW() and WEEKDAY() (WEEKDAY returns 1 for Sunday and 7 for Saturday), which the library page does not list.
| Function | Type | What it does | Example |
|---|---|---|---|
CONCATENATE | Text | Joins text values into one value | CONCATENATE("I","love","monday.com") => Ilovemonday.com |
LEFT | Text | Characters from the left of a text | LEFT("monday.com", 3) => mon |
LEN | Text | Number of characters in a text | LEN("hello") => 5 |
LOWER | Text | Converts text to lowercase | LOWER("Some STRING") => some string |
REPLACE | Text | Replaces part of a text with new text | REPLACE("Goat",1,2,"Fl") => Flat |
REPT | Text | Repeats a text a number of times | REPT("monday",3) => mondaymondaymonday |
RIGHT | Text | Characters from the right of a text | RIGHT("monday", 3) => day |
SEARCH | Text | Finds a text inside another text | IF(SEARCH("love","I love monday",1)>0,"Exist","Not") |
SUBSTITUTE | Text | Replaces matching text | SUBSTITUTE("goodmorning","morning","night") => goodnight |
TEXT | Text | Formats a value with a format pattern | TEXT(8500.6,"$#,##0.00") => $8,500.60 |
TRIM | Text | Removes extra spaces | TRIM(" I love MDY ") => I love MDY |
UPPER | Text | Converts text to uppercase | UPPER("monday.com") => MONDAY.COM |
AND | Logical | True if every condition is true | AND(3>1, 4>2) => true |
EXACT | Logical | True if two values are the same | EXACT({Status},{Status}) => true |
IF | Logical | Returns one value if a condition is true, another if not | IF({Deal Value}>100,"big deal","small deal") |
OR | Logical | True if any condition is true | OR(3>10, 4>2) => true |
XOR | Logical | Exclusive or of the conditions | XOR(3>0, 2>9) => true |
SWITCH | Logical | Returns the result paired with the matching value, with an optional default | SWITCH({Priority},"High",3,"Medium",2,"Low",1,0) |
ABS | Numeric | Absolute value | ABS(-1) => 1 |
AVERAGE | Numeric | Average of the numbers | AVERAGE(1,2,3) => 2 |
COUNT | Numeric | Counts the numeric items | COUNT(1,2,"a") => 2 |
DIVIDE | Numeric | One number divided by another | DIVIDE(10,5) => 2 |
LOG | Numeric | Logarithm of a number | LOG(16,2) => 4 |
MAX | Numeric | Largest value | MAX(1,3,5,9) => 9 |
MIN | Numeric | Smallest value | MIN(1,3,5,9) => 1 |
MINUS | Numeric | Difference of two numbers | MINUS(5,3) => 2 |
MOD | Numeric | Remainder of a division | MOD(10,3) => 1 |
MULTIPLY | Numeric | Product of two numbers | MULTIPLY(5,2) => 10 |
POWER | Numeric | A number raised to a power | POWER(2,3) => 8 |
ROUND | Numeric | Rounds to a number of digits | ROUND(1.123456,2) => 1.12 |
ROUNDDOWN | Numeric | Always rounds down | ROUNDDOWN(1.1,0) => 1 |
ROUNDUP | Numeric | Always rounds up | ROUNDUP(1.1,0) => 2 |
SQRT | Numeric | Square root | SQRT(9) => 3 |
SUM | Numeric | Adds the numbers | SUM(2,3,8) => 13 |
ADD_DAYS | Date and time | Adds days to a date | ADD_DAYS("2019-01-20", 5) |
DATE | Date and time | Builds a date from year, month, day | DATE(2018,5,30) |
DATEVALUE | Date and time | Turns a date written as text into a date | DATEVALUE("2025-11-12") |
DAY | Date and time | Day of the month | DAY({Creation Log}) => 31 |
DAYS | Date and time | Days between two dates (end first) | DAYS({End Date},{Start Date}) |
FORMAT_DATE | Date and time | Formats a date | FORMAT_DATE(TODAY(),"YYYY-MM-DD") |
HOUR | Date and time | Hour from 0 to 23 | HOUR(NOW()) |
HOURS_DIFF | Date and time | Difference between two Hour columns | HOURS_DIFF("23:00","20:00") => 03:00 |
ISOWEEKNUM | Date and time | ISO week number of a date | ISOWEEKNUM({Date}) |
MINUTE | Date and time | Minute from 0 to 59 | MINUTE(NOW()) |
MONTH | Date and time | Month of a date | MONTH({Creation Log}) => 7 |
SECOND | Date and time | Second from 0 to 59 | SECOND(NOW()) |
SUBTRACT_DAYS | Date and time | Subtracts days from a date | SUBTRACT_DAYS(TODAY(), 5) |
SUBTRACT_MINUTES | Date and time | Subtracts minutes from a date or time | SUBTRACT_MINUTES({Date}, 30) |
TODAY | Date and time | The current date | TODAY() |
WEEKNUM | Date and time | Week number of a date | WEEKNUM({Creation Log}) => 42 |
WORKDAY | Date and time | Adds working days to a date | WORKDAY({Date}, 20) |
WORKDAYS | Date and time | Working days between two dates (end first) | WORKDAYS({To},{From}) |
YEAR | Date and time | Year of a date | YEAR({Creation Log}) => 2018 |
PI | Constants | The value of pi | PI() => 3.14159 |
TRUE | Constants | Logical true | IF(3>5 = TRUE, "a", "b") => b |
FALSE | Constants | Logical false | FALSE |
Dates and times: monday.com says its date functions read date input in day/month order (May 12 as 12/5), show times on a 24-hour clock, and that date output varies. Wrap results in FORMAT_DATE() to control how they look, for example FORMAT_DATE(TODAY(),"YYYY-MM-DD").
Where the rules come from
Everything the builder and checker apply is from monday.com's support articles, read on October 17, 2026: The Formula Column (brackets, case, quotes, decimals, compatible columns, the AI assistant, limits, the 10,000-character cap, IFERROR not supported, LEN for blanks), Available functions in the Formula Column (the function library and examples) and Formula Use Cases (date, working-day, timeline, commission, inventory and business-hours examples, and the helper-column workaround for automations). The leading "=", semicolon and Excel-function checks are our additions for formulas pasted from spreadsheets.
Moving data into monday.com as well? The CRM import file checker tests a CSV against monday's import limits, and importing a CSV into monday.com covers the steps. To see what monday CRM costs for your team, use the monday CRM pricing calculator.
monday.com formula questions
How do I write an IF formula in monday.com?
Use IF(condition, value if true, value if false) and wrap column names in braces. For example IF({Status}="Done","Complete","Open") returns Complete for items marked Done and Open for everything else. Text, including status labels, goes in straight double quotes and must match the label exactly. You can nest IFs or use SWITCH for more than two outcomes.
How do I check if a column is empty in a monday.com formula?
monday.com recommends an IF check with LEN, because IFERROR is not supported. IF(LEN({Email})=0,"Missing","OK") returns Missing when the Email cell is blank. Put the same check in front of any calculation that would break on a blank cell, such as a division or a date difference, so empty rows show a blank instead of an error.
How do you write not equal in a monday.com formula?
Swap the two results of an IF. To return Open when the status is not Done, write IF({Status}="Done","Closed","Open"). monday.com's documentation only shows equals, greater than and less than comparisons, so the swapped IF is the safe form. EXACT() is another documented option when you compare two columns.
Why does my monday.com formula say Illegal formula?
The usual causes are curly quotes pasted from another app, a decimal written without a leading zero, unmatched brackets, a column name without braces or with the wrong case, an Excel-only function such as IFERROR or VLOOKUP, or a blank cell feeding a calculation. Paste the formula into the checker above to find and fix each one.
Can a monday.com formula change a status or trigger an automation?
No. A Formula column is read-only and calculated on the fly, so it cannot be an automation trigger and its result cannot be written into another column by itself. monday.com's workaround is to copy the formula result into a regular Numbers or Text column with a workflow, then point the automation, email or chart at that column.
Can a monday.com formula sum a whole column or read another board?
No. The Formula column calculates across one item (horizontally), not down a column, and it cannot read other boards, so VLOOKUP-style lookups are not possible. Use the column summary at the bottom for totals, and a Connect Boards plus Mirror column to bring a value in from another board.
Is there an AI formula builder in monday.com?
Yes, on accounts with monday AI enabled. In the Formula column editor, open the AI assistant tab, describe the calculation and insert the result. monday.com notes that the AI cannot read your board's labels, so you must replace label text with the exact labels from your board. This page's builder needs no AI credits or login.
How long can a monday.com formula be?
Up to 10,000 characters in the formula builder, according to monday.com's Formula Column article. Long nested IF chains are usually easier to maintain as one SWITCH, which also makes the formula shorter.
Board logic getting out of hand?
When formulas, mirrors and automations start depending on each other, a board needs a design, not another workaround. We build monday CRM boards with the calculations, helper columns and automations documented.