How to fix formula errors

How to fix the "Operator '+' cannot be applied to date and number" error

To fix it, you need to convert the number to a duration with the help of the Days function. If the number represents a month or a year, there is no way to convert it to a duration; you should adjust the formula expression to use the AdjustMonth or AdjustYear function accordingly.

For example:

[Date Column] + Days(1) - adds 1 day to the date.
[Date Column] + Days([Numeric column]) - converts the value from the "Numeric column" to a duration in days and adds it to the date.

If the number is constant, it’s possible to just add a "d" suffix at the end to convert it to a duration in days.

For example:

[Date Column] + 2d - adds 2 days to the date.

How to fix the "Operator '-' cannot be applied to date and number" error

For example:

[Date Column] - Days(1) - subtracts 1 day from the date.
[Date Column] - Days([Numeric column]) - converts the value from the "Numeric column" to a duration in days and subtracts it from the date.

If the number is constant, it’s possible to just add a "d" suffix at the end to convert it to a duration in days.

For example:

[Date Column] - 2d - subtracts 2 days from the date.

How to fix "Operator '+' can not be applied to timestamp and number" error

To fix it you need to convert number to duration with help of Days, Hours or Minutes function. If the number represents a month or a year, there is no way to convert it to a duration; you should adjust the formula expression to use the AdjustMonth or AdjustYear function accordingly.

For example:

[Timestamp Column] + Days(1) - adds 1 day to the timestamp.
[Timestamp Column] + Hours(3) - adds 3 hours to the timestamp.
[Timestamp Column] + Minutes(10) - adds 10 minutes to the timestamp.
[Timestamp Column] + Days([Numeric column]) - converts the value from "Numeric column" to duration in days and adds it to the timestamp.
[Timestamp Column] + Hours([Numeric column]) - converts the value from "Numeric column" to duration in hours and adds it to the timestamp.
[Timestamp Column] + Minutes([Numeric column]) - converts the value from "Numeric column" to duration in minutes and adds it to the timestamp.

If number is constant it’s possible just add a "d", "h" or "m" suffix at the end to convert it to duration in days, hours or minutes.

For example:

[Timestamp Column] + 2d - adds 2 days to the timestamp.
[Timestamp Column] + 1h - adds 1 hour days to the timestamp.
[Timestamp Column] + 25m - adds 25 minutes to the timestamp.

How to fix "Operator '-' can not be applied to timestamp and number" error

For example:

[Timestamp Column] - Days(1) - subtracts 1 day from the timestamp.
[Timestamp Column] - Hours(3) - subtracts 3 hours from the timestamp.
[Timestamp Column] - Minutes(10) - subtracts 10 minutes from the timestamp.
[Timestamp Column] - Days([Numeric column]) - converts the value from "Numeric column" to duration in days and subtracts it from the timestamp.
[Timestamp Column] - Hours([Numeric column]) converts the value from "Numeric column" to duration in hours and subtracts it from the timestamp.
[Timestamp Column] - Minutes([Numeric column]) converts the value from "Numeric column" to duration in minutes and subtracts it from the timestamp.

If number is constant it’s possible just add a "d", "h" or "m" suffix at the end to convert it to duration in days, hours or minutes.

For example:

[Timestamp Column] - 2d - subtracts 2 days from the timestamp.
[Timestamp Column] - 1h - subtracts 1 hour days from the timestamp.
[Timestamp Column] - 25m - subtracts 25 minutes from the timestamp.

How to fix "Expression expected to return text, found user" error

To fix it you need to convert user to text with help of UserToName function.

For example:

UserToName([Created By]) - returns the full name of the user listed in the Created By column.
UserToName(User()) - returns the full name of current user.

If an error occurs at position 0, then the entire formula is correct, but it returns the wrong type. So, please use the corresponding function to wrap the entire formula and convert the result to text.

How to fix "Operator '>=' can not be applied to timestamp and date" error

To fix it you need to convert timestamp to date with help of ToDate function.

For example:

ToDate([Timestamp Column]) >= [Date Column] - return true if date from "Timestamp column" great or equal date from "Date Column".

If for timestamp is used Now function you can replace it with Today function which return current date, so there is no need to convert it value to date.

How to fix the "Operator '=' cannot be applied to user and text" error

If the text resembles an email address, you can convert it to a user with the help of the ToUser function. Otherwise, you need to convert the user to text using the UserToName function.

For example:

User() = ToUser("john@doe.com') returns true if the current user has the email address john@doe.com.
UserToName([Record Owner]) = "John Doe" returns true if the record owner has the full name "John Doe".

How to fix "Operator '=' can not be applied to bool and text" error

If text is constant string like "No" or "Yes" or "True" or "False" or "Y" or "N" then replace it with true or false boolean constant accordingly.

For example:

[Boolean Column] = "Yes" - is invalid formula and should be replacted with following: [Boolean Column] = true
[Boolean Column] = "N" - is invalid formula and should be replacted with following: [Boolean Column] = false

Otherwise you’ve need to convert bool to text or convert text to bool.

How to fix "Expression expected to return number, found duration" error

To fix it you need to convert duration to number with help of ToDays, ToHours, ToMinutes or ToSeconds function depending on which value you need.

For example:

ToDays(Today()-[Date Column]) - returns number of days between todate and "Date Column".
ToHours([Duration Column]) - takes the duration value "2.5 hrs" stored in the duration column and returns the numeric value 2.5
ToMinutes([Duration Column]) - takes the duration value "45 mins" stored in the duration column and returns the numeric value 45
ToSeconds([Duration Column]) - takes the duration value "25 mins" stored in the duration column and returns the numeric value 1500

How to fix "Function 'If': argument" error

If function should return one type of value.

How to fix "Function 'Case': argument" error

Case function should return one type of value.

How to fix "Column not found" error

To fix it, you should ask the user to replace the not found column name with an existing table column. You can also offer possible options if any.

How to fix "Operator '=' can not be applied to bool and number" error

If number is constant number like "1" or "0" then replace it with true or false boolean constant accordingly.

For example:

[Boolean Column] = 1 - is invalid formula and should be replacted with following: [Boolean Column] = true
[Boolean Column] = 0 - is invalid formula and should be replacted with following: [Boolean Column] = false

Otherwise you’ve need to convert number to bool with help of ToBoolean function:

[Boolean Column] = ToBoolean([Numeric Column])

How to fix "If/Case expressions nested too deep" error

To fix it, you should replace multiple nested If functions:

If(condition1, result1, If(condition2, result2, If(conditionN, resultN, else-result)))

with a single "If" function containing multiple condition-result pairs:

If(condition1, result1, condition2, result2, conditionN, resultN, else-result)

For example, the following formula with nested "If" function:

If([Status]="Paid", [Paid Date], If([Due Date]>Today(), [Due Date], Today()))

can be replaced with the following formula:

If([Status]="Paid", [Paid Date], [Due Date]>Today(), [Due Date], Today())

If the 'If' function checks multiple values from the same column, you may replace it with the Case function.

It could also be the case that you need to simplify the formula in the columns used in that formula.

How to fix "Columns are not allowed in this context" error

The error "Columns are not allowed in this context" occurs when there is no current table record during formula calculation, preventing the use of any table column in that formula. To resolve this issue, you need to reconsider and rewrite your formula without using any table columns.

How to fix "Expected text" error

To fix it, use the Format function to convert a value to text. If a column reference is provided, TeamDesk derives formatting options from the column properties. Additionally, the Format function respects user-specific locale settings and supports a variety of standard and custom formatting patterns.

How to fix "Expression expected to return time, found date" error

There is no way to convert date to time. To resolve this issue, you need to reconsider and rewrite your formula.

How to fix "Expression expected to return duration, found number" error

To fix this error, you need to convert the number to a duration using the Days, Hours, Minutes, or Seconds function, depending on which numeric value the formula calculates.

For example:

Days(5) returns 5 days.
Hours(2.5) returns the duration value 2.5 hrs.
Minutes(37) returns the duration value 37 minutes.
Seconds(25) returns the duration value 25 seconds.

If the formula calculates numeric values in years, then there is no way to convert it to a duration. In such a case, it may be possible that an incorrect formula type is being used. Please consider changing the column type from Formula-Duration to Formula-Numeric in that case.

How to fix "Function does not exist" error

Function names are case-sensitive, so first, check if the function is named correctly. Here is the list of available functions in alphabetical order:

Abs, AdjustMonth, AdjustYear, All, Any, AppId, Acos, Asin, Atan, BackURL, Between, Begins, Browser, Case, Ceil, ColumnId, Concat, Contains, Cos, Cot, Count, Date, Day, DayOfWeek, DayOfYear, DeviceLatitude, DeviceLocation, DeviceLongitude, DeviceTimestamp, Distance, Ends, Exists,Exp, FileName, FileType, FirstDayOfMonth, FirstDayOfPeriod, FirstDayOfQuarter, FirstDayOfWeek, FirstDayOfYear, Floor, Frac, Guid, HMAC, Hash, Hour, If, In, Int, IsLeapDay, IsLeapYear, IsNull, LastDayOfMonth, LastDayOfPeriod, LastDayOfQuarter, LastDayOfWeek, LastDayOfYear, Latitude, Len, List, Ln, Log, Longitude, Lower, Max, Mid, Min, Minute, Mod, MonthsBetween, NextDayOfWeek, NotLeft, NotRight, Now, Nz, Parameter, ParentKey, Pi, PrevDayOfWeek, Proper, PV, Radians, Random, RecordId, Rem, Replace, Response, ResponseHeader, ResponseStatus, Right, Role, Round, Sin, Sqrt, Second, Seconds, Sum,Tan, Time, Timestamp, ToBoolean, ToDate, ToDays, ToHours, ToLocation, ToMinutes, ToNumber, ToSeconds, ToText, ToTimeOfDay, ToTimestamp, Today, Trim, Upper, URL, URLDecode, URLEncode, URLParam, User, UserToEmail, UserToName, YearsBetween, Year.

How to fix "Expression expected to return number, found text" error

To fix it, you need to convert the text to a number with the help of the ToNumber function.

For example:

ToNumber([Text column]) converts the value from the "Text column" into a number.
ToNumber("123.56") returns the numeric value 123.56.

If the text is a constant, you can simply convert it to the numeric literal. Numeric values should be written as is without the quotes. For example, 123.56.

How to fix "Operator can not be applied to number and duration" error

To resolve this issue, you have two options:

Convert the duration to a number using the ToDays, ToHours, ToMinutes, or ToSeconds function, depending on the desired value.
Convert the number to a duration using the Days, Hours, Minutes, or Seconds function, depending on the desired value.

How to fix "Operator can not be applied to number and text" error

To resolve this issue, you need to convert the text to a number using the ToNumber function.

For example:

ToNumber([Text column]) converts the value from the "Text column" into a number.
ToNumber("123.56") returns the numeric value 123.56.

How to fix "FileName() is not allowed in this context" error

This function can only be used in the Call-URL action body section.

To resolve this issue, you need to reconsider and rewrite your formula without using FileName function.

To obtain a file name from another location, please consider using the following formula:

Left([File Attachment Column], ";") - This formula returns a file name for the attachment stored in the "File Attachment Column".

How to fix "FileType() is not allowed in this context" error

This function can only be used in the Call-URL action body section.

To resolve this issue, you need to reconsider and rewrite your formula without using FileType function.

How to fix "Response() is not allowed in this context" error

This function can only be used in the Call-URL action or Webhooks.

To resolve this issue, you need to reconsider and rewrite your formula without using Response function.

How to fix "Expression expected to return time, found timestamp" error

To resolve this issue, you need to convert the timestamp to a time using the ToTimeOfDay function.

For example:

ToTimeOfDay([Timestamp Column]) returns the time value from the timestamp.

How to fix the "Expression expected to return text, found number" error

To resolve this issue, you need to convert the number to text using the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

Format([Numeric Column]) converts the numeric value stored in the "Numeric Column" to a text value.
ToText(5+5) returns "10.000000".

How to fix the "Expression expected to return text, found date" error

To resolve this issue, you need to convert the date to text using the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

Format([Date Column]) converts the date value stored in the "Date Column" to a text value.
ToText(#2013-03-25#) returns "2013-03-25".

How to fix the "Expression expected to return text, found time" error

To resolve this issue, you need to convert the time to text using the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

Format([Time Column]) converts the time value stored in the "Time Column" to a text value.
ToText(#10:15#) returns "10:15:00".

How to fix the "Expression expected to return text, found timestamp" error

To resolve this issue, you need to convert the timestamp to text using the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

Format([Timestamp Column]) converts the timestamp value stored in the "Timestamp Column" to a text value.
ToText(#2013-03-25 10:15#) returns "2013-03-25 10:15:00".

How to fix the "Expression expected to return text, found duration" error

To resolve this issue, you need to convert the duration to text using the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

Format([Duration Column]) converts the duration value stored in the "Duration Column" to a text value.
ToText(3h) returns "03:00:00".

How to Fix the "Operator '=' cannot be applied to null" error

To resolve this issue, you need to use the IsNull function instead.

For example:

IsNull([Column Name]) returns true if the column is empty; otherwise, it returns false.

How to Fix the "Operator '<>' cannot be applied to null" error

To resolve this issue, you need to use the IsNull function instead.

For example:

not IsNull([Column Name]) returns true if the column is not empty; otherwise, it returns false.

How to fix the "Expression expected to return number, found text" error

To resolve this issue, you need to convert the text to number using the ToNumber function.

For example:

ToNumber([Text Column]) converts the text representation of a numeric value stored in the "Text Column" to a numeric value.

How to fix the "Expression expected to return date, found text" error

To resolve this issue, you need to convert the text to date using the ToDate function.

For example:

ToDate([Text Column]) converts the text represenatation of a date value stored in the "Text Column" to a date value.

How to fix "Unexpected ')'" error

To fix the problem, check the entire formula for correct usage of round brackets.

How to fix "Expected '('" error

If the word at the error position equals yes, no, checked, or unchecked, it should most likely be replaced with the boolean literals true or false.

Otherwise, please check if the word at the error position corresponds to one of the available functions, and if so, suggest using the proper function.

How to fix "expected date, found number" error

There is no way to convert a number to a date. You should rethink your formula.

How to fix "Parameters are not allowed in this context" error

This function works in filter formulas only. It is used for the Ask the User option.

How to fix "Operator 'or' can not be applied to text and text" error

Most likely, the formula is trying to return the first non-blank value. If so, please consider using the Nz function instead.

For example:

Nz([One Text Column], [Another Text Column]) returns the value of "One Text Column" if it’s not blank. Otherwise, it returns the value of "Another Text Column".

How to fix "Unexpected 'Or'" error

There is no Or function. Please rewrite the formula using the logical operator or instead.

For example:

Instead of using the Or(condition1, condition2) function, please use the following condition1 or condition2 formula.

How to fix "Unexpected 'And'" error

There is no And function. Please rewrite the formula using the logical operator and instead.

For example:

Instead of using the And(condition1, condition2) function, please use the following condition1 and condition2 formula.

How to fix "Unexpected 'Not'" error

There is no Not function. Please rewrite the formula using the logical operator not instead.

For example:

Instead of using the Not(condition1) function, please use the following not condition formula.

How to fix "Function 'Format': expected column reference" error

If an expression is passed into the Format function as the first argument, then to fix the error you should pass a format string as a second parameter as well. This way, the system will know how to convert a value to text.

For example:

Format(#2009-06-15#, "D") returns the text value "Monday, June 15, 2009" for the "English (United States)" user locale.
Format(#2012-02-14#, "MM/yy") takes the date value 2012-02-14 and returns the text value "02/12".
Format(1500.63,"$#") returns the text value "$15001".
Format(0.3,"$#") returns a blank text value with the unit sign "$".
Format(1500.63,"#,#") returns the text value "1,501".
Format(0.3,"#,#") returns a blank text value.
Format(1500.63,"#,0") returns the text value "1,501".
Format(0.3,"#,0") returns the text value "0".
Format(1500.63,"0") returns the text value "1501".
Format(0.3,"0") returns the text value "0".
Format(1.63,"0%") returns the text value "163%".
Format(0.0003,"0%") returns the text value "0%".
Format(2.0039900, "$#,#.####") returns the text value "$2.004".
Format(0.000002, "$#,#.####") returns a blank text value.
Format(-5.204, "-$#,0.00") returns the text value "-$5.20".
Format(0.003, "-$#,0.00") returns the text value "$0.00".
Format(#10:15:11#, "t") returns the text value "10:15 AM".
Format(#10:15:11#, "T") returns the text value "10:15:11 AM".
Format(#2012-11-05 09:18:06#, "g") returns the text value "11/5/2012 9:18 AM".
Format(#2012-11-05 09:18:06#, "G") returns the text value "11/5/2012 9:18:06 AM".
Format(#2012-11-05 09:18:06#, "f") returns the text value "Monday, November 05, 2012 9:18 AM".
Format(#2012-11-05 09:18:06#, "F") returns the text value "Monday, November 05, 2012 9:18:06 AM".

How to fix "Prefix Related is not allowed in this context" error

The error "Prefix Related is not allowed in this context" occurs when you are trying to reference a related column in a location where it’s not permitted. At present, you can use the Related prefix in the summary column filter condition formula and the overridden record picker filter condition formula. To resolve this issue, you will need to reevaluate and rewrite your formula, avoiding the use of the Related prefix.