Returns a Yes/No
expression, as follows:
-
TRUE
if all conditions areTRUE
. -
FALSE
if at least one condition isFALSE
.
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 beTRUE
.IN([Sales Contact], ...)
matches only if the row'sSales 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 theIN()
above to match rows with the current app user as the designatedSales Contact
and rows with no designated contact.NOT([DO NOT CALL])
omits rows with aDO NOT CALL
column value ofTRUE
.[Last Contact] < (TODAY() - 30)
matches only rows with aLast 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)
)
AND(..., ...)
: both conditions must be true.([End] > [Start])
: new end date must be at least one day after the start date.(COUNT(...) = 0)
: the enclosedFILTER()
must find no matching rows; i.e., no existing start/end ranges may include the new range.FILTER("MyTable", ...)
: find rows inMyTable
that match criteria.OR(..., ..., ...)
: at least one condition must be true.AND(..., ...)
: both conditions must be true.([Start] >= [_THISROW].[Start])
: existing start date occurs on or after new start date.([Start] <= [_THISROW].[End])
: existing start date occurs before or on new end date.AND(..., ...)
: both conditions must be true.([End] >= [_THISROW].[Start])
: existing end date occurs on or after new start date.([End] <= [_THISROW].[End])
: existing end date occurs before or on new end date.AND(..., ...)
: both conditions must be true.([Start] < [_THISROW].[Start])
: existing start date occurs before new start date.([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
- AYes/No
expression that results inTRUE
orFALSE
. At least twocondition
arguments are required.