AND()

Returns a Yes/No expression, as follows:

• TRUE if all conditions are TRUE.

• FALSE if at least one condition is FALSE.

Sample usage

AND(FALSE, FALSE) returns FALSE

AND(FALSE, TRUE) returns FALSE

AND(TRUE, FALSE) returns FALSE

AND(TRUE, TRUE) returns TRUE

AND(ISNOTBLANK([Grade]), ([Grade] = "A")) returns TRUE only if the Grade column value is both present and has a value of exactly A; otherwise FALSE. See also: ISNOTBLANK()

AND(([Name] = "Acme"), ([Rating] > 3), [Preferred?]) returns TRUE only if the row is for the company named Acme that has a rating of 3 or higher and is a preferred vendor; FALSE otherwise.

AND((LEN([_THIS]) > 3), CONTAINS([_THIS], "@")) returns TRUE if the current column value (such as with Valid_If) is at least three characters long and contains at least one @ symbol. See also: CONTAINS(), LEN()

Choose stale sales prospects

Choose rows in a table, perhaps with the FILTER() or SELECT() functions, or by a slice or security filter, that describe the current app user's sales prospects that want to be contacted but haven't been recently:

AND(
  IN([Sales Contact], LIST(USEREMAIL(), "")),
  NOT([DO NOT CALL]),
  ([Last Contact] < (TODAY() - 30))
)

• AND(..., ..., ...) requires all conditions must be TRUE.
• IN([Sales Contact], ...) matches only if the row's Sales Contact column value occurs in the constructed list.
• LIST(USEREMAIL(), "") constructs a list containing the current app user's email address and a blank value, allowing the IN() above to match rows with the current app user as the designated Sales Contact and rows with no designated contact.
• NOT([DO NOT CALL]) omits rows with a DO NOT CALL column value of TRUE.
• [Last Contact] < (TODAY() - 30) matches only rows with a Last Contact date more than 30 days in the past.

See also: IN(), LIST(), NOT(), TODAY(), USEREMAIL()

Validate non-overlapping date range

Validate a new row's date range and ensure it does not overlap date ranges defined by existing rows in the table:

AND(
 ([End] > [Start]),
 (COUNT(
   FILTER(
     "MyTable",
     OR(
       AND(
         ([Start] >= [_THISROW].[Start]),
         ([Start] <= [_THISROW].[End])
       ),
       AND(
         ([End] >= [_THISROW].[Start]),
         ([End] <= [_THISROW].[End])
       )
       AND(
         ([Start] < [_THISROW].[Start]),
         ([End] > [_THISROW].[End])
       )
     )
   )
 ) = 0)
)

1. AND(..., ...): both conditions must be true.
2. ([End] > [Start]): new end date must be at least one day after the start date.
3. (COUNT(...) = 0): the enclosed FILTER() must find no matching rows; i.e., no existing start/end ranges may include the new range.
4. FILTER("MyTable", ...): find rows in MyTable that match criteria.
5. OR(..., ..., ...): at least one condition must be true.
6. AND(..., ...): both conditions must be true.
7. ([Start] >= [_THISROW].[Start]): existing start date occurs on or after new start date.
8. ([Start] <= [_THISROW].[End]): existing start date occurs before or on new end date.
9. AND(..., ...): both conditions must be true.
10. ([End] >= [_THISROW].[Start]): existing end date occurs on or after new start date.
11. ([End] <= [_THISROW].[End]): existing end date occurs before or on new end date.
12. AND(..., ...): both conditions must be true.
13. ([Start] < [_THISROW].[Start]): existing start date occurs before new start date.
14. ([End] > [_THISROW].[End]): existing end date occurs after new end date

If an existing date range starts in (6-8), ends in (9-11), or encloses (12-14) the new range, the filter selects the row (5) and returns the list of selected rows (4). A non-empty list means at least one existing date range overlaps the new range.

If the new row's end date properly comes after the start date (2) and the list of selected rows is empty (3), the expression is true (1), meaning the new date is valid and does not conflict with any existing dates.

See also: COUNT(), FILTER(), OR()

Syntax

AND(condition1, condition2, [condition3 ...])

• condition - A Yes/No expression that results in TRUE or FALSE. At least two condition arguments are required.

See also

NOT()

OR()