Field - Formula V2

Field - Formula V2

Explore Layer's formula V2 capabilities for date, text, and math functions. Learn how to use DATE, CONCATENATE, ROUND, and more to enhance your data calculations.

Mike Lee

Below is a complete reference for all available formula functions in Layer. Each entry includes the syntax, a description, parameters, return type, examples, and related functions. Read the technical documentation in Layer →

Array Functions

Functions for working with arrays and collections of values.

ARRAY(value1, value2, ...)

Creates an array from a list of values.

Parameters:

  • value1, value2, ... (any type): Values to include in the array. All values must be of the same type.

Returns: array.

Examples:

ARRAY(1, 2, 3) → [1, 2, 3]
ARRAY("apple", "banana", "cherry") → ["apple", "banana", "cherry"]
ARRAY(TRUE, FALSE, TRUE) → [true, false, true]

Related: ARRAYAT, ARRAYLENGTH

ARRAYAT(array, index)

Gets the value at a specified index in an array.

Parameters:

  • array (array): The array to access

  • index (number): Zero-based index position

Returns: any. The value at the specified index, or null if out of bounds.

Examples:

ARRAYAT([1, 2, 3], 0) → 1
ARRAYAT([1, 2, 3], 2) → 3
ARRAYAT(["apple", "banana"], 5) → null

Related: FIRST, LAST, ARRAYLENGTH

ARRAYFLATTEN(array)

Expands an array of objects with quantity into a flat array.

Parameters:

  • array (array of objects): Array containing objects with a quantity property

Returns: array. Flattened array with each object repeated according to its quantity, with quantity set to 1.

Examples:

ARRAYFLATTEN([
  {name: "apple", quantity: 3},
  {name: "banana", quantity: 2}
]) → [
  {name: "apple", quantity: 1},
  {name: "apple", quantity: 1},
  {name: "apple", quantity: 1},
  {name: "banana", quantity: 1},
  {name: "banana", quantity: 1}
]

Related: ARRAY, UNIQUE

ARRAYJOIN(array, delimiter)

Joins an array of strings into a single string.

Parameters:

  • array (array of strings): Array containing string values

  • delimiter (string): String to place between joined values

Returns: string. Joined string.

Examples:

ARRAYJOIN(["apple", "banana", "cherry"], ", ") → "apple, banana, cherry"
ARRAYJOIN(["a", "b", "c"], "-") → "a-b-c"
ARRAYJOIN(["hello", "world"], " ") → "hello world"

Related: ARRAY, CONCATENATE

ARRAYLENGTH(array)

Returns the number of items in an array.

Parameters:

  • array (array): The array to measure

Returns: number. Count of items in the array.

Examples:

ARRAYLENGTH([1, 2, 3]) → 3
ARRAYLENGTH(["apple", "banana"]) → 2
ARRAYLENGTH([]) → 0

Related: ARRAYAT, FIRST, LAST

AVERAGE(numbers)

Calculates the average (mean) of an array of numbers.

Parameters:

  • numbers (array of numbers): Array of numeric values to average

Returns: number. The arithmetic mean of the values.

Examples:

AVERAGE([10, 20, 30]) → 20
AVERAGE([5, 15, 25, 35]) → 20
AVERAGE([1.5, 2.5, 3.5]) → 2.5

Related: SUM, MEDIAN, MIN, MAX

COUNTEMPTY(values)

Counts the number of empty values in an array. An empty value is anything that is not a number, a string that is empty or contains only whitespace, a falsy boolean value, null, or undefined.

Parameters:

  • values (array): Array of values to evaluate

Returns: number. Count of empty values.

Examples:

COUNTEMPTY([1, "", 3, null]) → 2
COUNTEMPTY(["hello", "", "  ", "world"]) → 2
COUNTEMPTY([TRUE, FALSE, TRUE]) → 1

Related: COUNTNOTEMPTY

COUNTNOTEMPTY(values)

Counts the number of non-empty values in an array. A non-empty value is a number, a string that is not empty and contains more than whitespace, or a truthy boolean value.

Parameters:

  • values (array): Array of values to evaluate

Returns: number. Count of non-empty values.

Examples:

COUNTNOTEMPTY([1, "", 3, null]) → 2
COUNTNOTEMPTY(["hello", "", "world"]) → 2
COUNTNOTEMPTY([TRUE, FALSE, TRUE]) → 2

Related: COUNTEMPTY

FIRST(array)

Gets the value at the first index in an array.

Parameters:

  • array (array): The array to access

Returns: any. The first element, or null if the array is empty.

Examples:

FIRST([1, 2, 3]) → 1
FIRST(["apple", "banana", "cherry"]) → "apple"
FIRST([]) → null

Related: LAST, ARRAYAT, ARRAYLENGTH

LAST(array)

Gets the value at the last index in an array.

Parameters:

  • array (array): The array to access

Returns: any. The last element, or null if the array is empty.

Examples:

LAST([1, 2, 3]) → 3
LAST(["apple", "banana", "cherry"]) → "cherry"
LAST([]) → null

Related: FIRST, ARRAYAT, ARRAYLENGTH

MATRIXMULTIPLY(array1, array2)

Multiplies two arrays of numbers element-wise. If arrays have different lengths, the shorter is padded with 0s.

Parameters:

  • array1 (array of numbers): First array of numbers

  • array2 (array of numbers): Second array of numbers

Returns: array of numbers. Element-wise product.

Examples:

MATRIXMULTIPLY([1, 2, 3], [2, 3, 4]) → [2, 6, 12]
MATRIXMULTIPLY([5, 10], [2, 3, 4]) → [10, 30, 0]
MATRIXMULTIPLY([1, 1, 1], [1, 1, 1]) → [1, 1, 1]

Related: PRODUCT, ARRAY

MAX(numbers)

Returns the maximum value from a list of numbers.

Parameters:

  • numbers (array of numbers): Array of numeric values to evaluate

Returns: number. The largest value in the array.

Examples:

MAX([10, 20, 30]) → 30
MAX([5, 15, 25, 35]) → 35
MAX([-10, -5, -20]) → -5

Related: MIN, AVERAGE, RANGE

MEDIAN(numbers)

Calculates the median of an array of numbers.

Parameters:

  • numbers (array of numbers): Array of numeric values

Returns: number. The median value (middle value when sorted).

Examples:

MEDIAN([1, 2, 3, 4, 5]) → 3
MEDIAN([10, 20, 30]) → 20
MEDIAN([1, 2, 3, 4]) → 2.5

Related: AVERAGE, MIN, MAX

MIN(numbers)

Returns the minimum value from a list of numbers.

Parameters:

  • numbers (array of numbers): Array of numeric values to evaluate

Returns: number. The smallest value in the array.

Examples:

MIN([10, 20, 30]) → 10
MIN([5, 15, 25, 35]) → 5
MIN([-10, -5, -20]) → -20

Related: MAX, AVERAGE, RANGE

PRODUCT(numbers)

Multiplies all numbers in an array.

Parameters:

  • numbers (array of numbers): Array of numeric values to multiply

Returns: number. The product of all values.

Examples:

PRODUCT([2, 3, 4]) → 24
PRODUCT([5, 2]) → 10
PRODUCT([1, 1, 1]) → 1

Related: SUM, MATRIXMULTIPLY

RANGE(numbers)

Returns the difference between the highest and lowest numerical values in an array.

Parameters:

  • numbers (array of numbers): Array of numeric values

Returns: number. The range (max minus min).

Examples:

RANGE([10, 20, 30]) → 20
RANGE([5, 15, 25]) → 20
RANGE([1, 100]) → 99

Related: MAX, MIN, AVERAGE

SUM(numbers)

Sums an array of numerical values.

Parameters:

  • numbers (array of numbers): Array of numeric values to sum

Returns: number. The sum of all values.

Examples:

SUM([1, 2, 3]) → 6
SUM([10, 20, 30]) → 60
SUM([1.5, 2.5]) → 4

Related: AVERAGE, PRODUCT, MIN, MAX

UNIQUE(values)

Consolidates an array by combining equivalent values and storing the quantity on the returned value.

Parameters:

  • values (array): Array of values to consolidate

Returns: array of objects. Array where each unique value appears once with a quantity property.

Examples:

UNIQUE([1, 2, 1, 3, 2, 1]) → [
  {value: 1, quantity: 3},
  {value: 2, quantity: 2},
  {value: 3, quantity: 1}
]

UNIQUE(["apple", "banana", "apple"]) → [
  {value: "apple", quantity: 2},
  {value: "banana", quantity: 1}
]

Related: ARRAY, ARRAYFLATTEN, ARRAYLENGTH

Text Functions

Functions for working with strings and text manipulation.

CONCATENATE(text1, text2, ...)

Concatenates two or more strings into one.

Parameters:

  • text1, text2, ... (string): Strings to concatenate

Returns: string. Combined string.

Examples:

CONCATENATE("Hello", " ", "World") → "Hello World"
CONCATENATE("First", "Last") → "FirstLast"
CONCATENATE("a", "b", "c", "d") → "abcd"

Related: ARRAYJOIN, SUBSTITUTE

DATE(value)

Converts a value to a date.

Parameters:

  • value (string, number, or date): Value to convert. Accepts ISO 8601 strings, timestamps, or existing date values.

Returns: date. The parsed date object.

Examples:

DATE("2024-05-06") → 2024-05-06T00:00:00Z
DATE(1714982400000) → 2024-05-06T00:00:00Z
DATE("2024-05-06T15:30:00Z") → 2024-05-06T15:30:00Z

Related: DATETOSTRING, NOW

ENCODEURICOMPONENT(text)

Replaces characters with encoded equivalents for use in constructing URLs or URIs.

Parameters:

  • text (string): String to encode for URI use

Returns: string. URL-encoded string.

Examples:

ENCODEURICOMPONENT("hello world") → "hello%20world"
ENCODEURICOMPONENT("user@example.com") → "user%40example.com"
ENCODEURICOMPONENT("path/to/resource") → "path%2Fto%2Fresource"

Related: CONCATENATE, LEFT, RIGHT

EQUALS(value1, value2)

Checks if two values are equal. Inputs must be of the same type to be considered equal.

Parameters:

  • value1 (any): First value to compare

  • value2 (any): Second value to compare

Returns: boolean. TRUE if equal, FALSE otherwise.

Examples:

EQUALS("apple", "apple") → TRUE
EQUALS(5, 5) → TRUE
EQUALS("5", 5) → FALSE
EQUALS(TRUE, TRUE) → TRUE

Related: NOTEQUALS, IF

LEFT(text, count)

Extracts a substring of the specified size from the start of a string.

Parameters:

  • text (string): The string to extract from

  • count (number): Number of characters to extract from the left

Returns: string. Substring of the specified length.

Examples:

LEFT("hello world", 5) → "hello"
LEFT("Layer", 1) → "L"
LEFT("abc", 10) → "abc"

Related: RIGHT, MID, LENGTH

LENGTH(text)

Returns the number of characters in a string.

Parameters:

  • text (string): String to measure

Returns: number. Number of characters.

Examples:

LENGTH("hello") → 5
LENGTH("Layer") → 5
LENGTH("") → 0
LENGTH("hello world") → 11

Related: LEFT, RIGHT, MID, ARRAYLENGTH

LOWER(text)

Converts a string to all lowercase.

Parameters:

  • text (string): String to convert

Returns: string. Lowercase string.

Examples:

LOWER("Hello World") → "hello world"
LOWER("LAYER") → "layer"
LOWER("123ABC") → "123abc"

Related: UPPER, TRIM

MID(text, start_index, length)

Extracts a substring from a string.

Parameters:

  • text (string): The string to extract from

  • start_index (number): Zero-based starting position

  • length (number): Number of characters to extract

Returns: string. Extracted substring.

Examples:

MID("hello world", 0, 5) → "hello"
MID("hello world", 6, 5) → "world"
MID("Layer", 1, 3) → "aye"

Related: LEFT, RIGHT, LENGTH

NOTEQUALS(value1, value2)

Checks if two values are not equal. Inputs must be of the same type for proper comparison.

Parameters:

  • value1 (any): First value to compare

  • value2 (any): Second value to compare

Returns: boolean. TRUE if not equal, FALSE if equal.

Examples:

NOTEQUALS("apple", "banana") → TRUE
NOTEQUALS(5, 5) → FALSE
NOTEQUALS("5", 5) → TRUE
NOTEQUALS(TRUE, FALSE) → TRUE

Related: EQUALS, IF

REGEXEXTRACT(text, pattern)

Extracts a substring that matches a regular expression.

Parameters:

  • text (string): The string to search in

  • pattern (string): Regular expression pattern

Returns: string. First match found, or empty string if no match.

Examples:

REGEXEXTRACT("hello123world", "\d+") → "123"
REGEXEXTRACT("user@example.com", "[a-z]+") → "user"
REGEXEXTRACT("no match here", "\d+") → ""

Related: REGEXMATCH, REGEXREPLACE, SEARCH

REGEXMATCH(text, pattern)

Checks if a string matches a regular expression.

Parameters:

  • text (string): The string to test

  • pattern (string): Regular expression pattern

Returns: boolean. TRUE if pattern matches, FALSE otherwise.

Examples:

REGEXMATCH("hello123world", "\d+") → TRUE
REGEXMATCH("user@example.com", "^[a-z]+@") → TRUE
REGEXMATCH("no numbers", "\d+") → FALSE

Related: REGEXEXTRACT, REGEXREPLACE, SEARCH

REGEXREPLACE(text, pattern, replacement)

Replaces a substring that matches a regular expression.

Parameters:

  • text (string): The string to search in

  • pattern (string): Regular expression pattern to match

  • replacement (string): Replacement string

Returns: string. String with replacements made.

Examples:

REGEXREPLACE("hello123world456", "\d+", "X") → "helloXworldX"
REGEXREPLACE("user@example.com", "([a-z]+)@", "$1 at ") → "user at example.com"
REGEXREPLACE("HELLO", "[A-Z]", "x") → "xxxxx"

Related: REGEXMATCH, REGEXEXTRACT, SUBSTITUTE

REPEAT(text, count)

Repeats a string a specified number of times.

Parameters:

  • text (string): String to repeat

  • count (number): Number of times to repeat

Returns: string. Repeated string.

Examples:

REPEAT("ab", 3) → "ababab"
REPEAT("x", 5) → "xxxxx"
REPEAT("hello", 1) → "hello"

Related: CONCATENATE, ARRAYJOIN

RIGHT(text, count)

Extracts a substring of the specified size from the end of a string.

Parameters:

  • text (string): The string to extract from

  • count (number): Number of characters to extract from the right

Returns: string. Substring of the specified length.

Examples:

RIGHT("hello world", 5) → "world"
RIGHT("Layer", 2) → "er"
RIGHT("abc", 10) → "abc"

Related: LEFT, MID, LENGTH

SEARCH(text, search_text)

Searches a string for another string and returns the index position of the found string.

Parameters:

  • text (string): The string to search in

  • search_text (string): The string to search for

Returns: number. Zero-based index of first occurrence, or -1 if not found.

Examples:

SEARCH("hello world", "world") → 6
SEARCH("hello world", "o") → 4
SEARCH("hello world", "xyz") → -1

Related: REGEXMATCH, REGEXEXTRACT, SUBSTITUTE

SUBSTITUTE(text, search_text, replacement_text)

Substitutes a string with another string.

Parameters:

  • text (string): The string to search in

  • search_text (string): The string to find and replace

  • replacement_text (string): The replacement string

Returns: string. String with substitutions made.

Examples:

SUBSTITUTE("hello world", "world", "Layer") → "hello Layer"
SUBSTITUTE("aaa", "a", "b") → "bbb"
SUBSTITUTE("hello", "xyz", "abc") → "hello"

Related: REGEXREPLACE, CONCATENATE

TRIM(text)

Trims whitespace from a string.

Parameters:

  • text (string): String to trim

Returns: string. String with leading and trailing whitespace removed.

Examples:

TRIM("  hello  ") → "hello"
TRIM("  Layer  ") → "Layer"
TRIM("no spaces") → "no spaces"

Related: LOWER, UPPER, LENGTH

UPPER(text)

Converts a string to all uppercase.

Parameters:

  • text (string): String to convert

Returns: string. Uppercase string.

Examples:

UPPER("hello world") → "HELLO WORLD"
UPPER("Layer") → "LAYER"
UPPER("123abc") → "123ABC"

Related: LOWER, TRIM

Date Functions

Functions for working with dates and times.

DATEADDDAYS(date, days)

Adds a specified number of days to a date.

Parameters:

  • date (date): The starting date

  • days (number): Number of days to add (can be negative)

Returns: date. New date after adding days.

Examples:

DATEADDDAYS(2024-05-06, 7) → 2024-05-13
DATEADDDAYS(2024-05-06, -7) → 2024-04-29
DATEADDDAYS(2024-05-06, 0) → 2024-05-06

Related: DATEADDWEEKS, DATEADDMONTHS, DATESUBTRACTDAYS

DATEADDHOURS(date, hours)

Adds a specified number of hours to a date.

Parameters:

  • date (date): The starting date

  • hours (number): Number of hours to add (can be negative)

Returns: date. New date after adding hours.

Examples:

DATEADDHOURS(2024-05-06T12:00:00Z, 5) → 2024-05-06T17:00:00Z
DATEADDHOURS(2024-05-06T12:00:00Z, -2) → 2024-05-06T10:00:00Z

Related: DATEADDMINUTES, DATEADDSECONDS, DATESUBTRACTHOURS

DATEADDMINUTES(date, minutes)

Adds a specified number of minutes to a date.

Parameters:

  • date (date): The starting date

  • minutes (number): Number of minutes to add (can be negative)

Returns: date. New date after adding minutes.

Examples:

DATEADDMINUTES(2024-05-06T12:00:00Z, 30) → 2024-05-06T12:30:00Z
DATEADDMINUTES(2024-05-06T12:00:00Z, -15) → 2024-05-06T11:45:00Z

Related: DATEADDHOURS, DATEADDSECONDS, DATESUBTRACTMINUTES

DATEADDMONTHS(date, months)

Adds a specified number of months to a date.

Parameters:

  • date (date): The starting date

  • months (number): Number of months to add (can be negative)

Returns: date. New date after adding months.

Examples:

DATEADDMONTHS(2024-05-06, 3) → 2024-08-06
DATEADDMONTHS(2024-05-06, -1) → 2024-04-06
DATEADDMONTHS(2024-01-31, 1) → 2024-02-29

Related: DATEADDYEARS, DATEADDWEEKS, DATESUBTRACTMONTHS

DATEADDSECONDS(date, seconds)

Adds a specified number of seconds to a date.

Parameters:

  • date (date): The starting date

  • seconds (number): Number of seconds to add (can be negative)

Returns: date. New date after adding seconds.

Examples:

DATEADDSECONDS(2024-05-06T12:00:00Z, 60) → 2024-05-06T12:01:00Z
DATEADDSECONDS(2024-05-06T12:00:00Z, -30) → 2024-05-06T11:59:30Z

Related: DATEADDMINUTES, DATEADDHOURS, DATESUBTRACTSECONDS

DATEADDWEEKS(date, weeks)

Adds a specified number of weeks to a date.

Parameters:

  • date (date): The starting date

  • weeks (number): Number of weeks to add (can be negative)

Returns: date. New date after adding weeks.

Examples:

DATEADDWEEKS(2024-05-06, 2) → 2024-05-20
DATEADDWEEKS(2024-05-06, -1) → 2024-04-29

Related: DATEADDDAYS, DATEADDMONTHS, DATESUBTRACTWEEKS

DATEADDYEARS(date, years)

Adds a specified number of years to a date.

Parameters:

  • date (date): The starting date

  • years (number): Number of years to add (can be negative)

Returns: date. New date after adding years.

Examples:

DATEADDYEARS(2024-05-06, 1) → 2025-05-06
DATEADDYEARS(2024-05-06, -2) → 2022-05-06

Related: DATEADDMONTHS, DATEADDWEEKS, DATESUBTRACTYEARS

DATEISAFTER(date1, date2)

Checks if the first date is after the second date.

Parameters:

  • date1 (date): First date to compare

  • date2 (date): Second date to compare

Returns: boolean. TRUE if date1 is after date2, FALSE otherwise.

Examples:

DATEISAFTER(2024-05-10, 2024-05-06) → TRUE
DATEISAFTER(2024-05-06, 2024-05-10) → FALSE
DATEISAFTER(2024-05-06, 2024-05-06) → FALSE

Related: DATEISBEFORE, DATEISSAME

DATEISBEFORE(date1, date2)

Checks if the first date is before the second date.

Parameters:

  • date1 (date): First date to compare

  • date2 (date): Second date to compare

Returns: boolean. TRUE if date1 is before date2, FALSE otherwise.

Examples:

DATEISBEFORE(2024-05-06, 2024-05-10) → TRUE
DATEISBEFORE(2024-05-10, 2024-05-06) → FALSE
DATEISBEFORE(2024-05-06, 2024-05-06) → FALSE

Related: DATEISAFTER, DATEISSAME

DATEISSAME(date1, date2)

Checks if two dates are the same.

Parameters:

  • date1 (date): First date to compare

  • date2 (date): Second date to compare

Returns: boolean. TRUE if dates are identical, FALSE otherwise.

Examples:

DATEISSAME(2024-05-06T12:00:00Z, 2024-05-06T12:00:00Z) → TRUE
DATEISSAME(2024-05-06T12:00:00Z, 2024-05-06T12:00:01Z) → FALSE

Related: DATEISSAMEDAY, DATEISSAMEWEEK, DATEISSAMEMONTH

DATEISSAMEDAY(date1, date2)

Checks if two dates are on the same day.

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if dates are on the same calendar day, FALSE otherwise.

Examples:

DATEISSAMEDAY(2024-05-06T12:00:00Z, 2024-05-06T15:30:00Z) → TRUE
DATEISSAMEDAY(2024-05-06T12:00:00Z, 2024-05-07T12:00:00Z) → FALSE

Related: DATEISSAMEWEEK, DATEISSAMEMONTH, DATEISSAMEYEAR

DATEISSAMEHOUR(date1, date2)

Checks if two dates are in the same hour.

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if in the same hour, FALSE otherwise.

Examples:

DATEISSAMEHOUR(2024-05-06T12:00:00Z, 2024-05-06T12:30:00Z) → TRUE
DATEISSAMEHOUR(2024-05-06T12:00:00Z, 2024-05-06T13:00:00Z) → FALSE

Related: DATEISSAMEMINUTE, DATEISSAMEDAY

DATEISSAMEMINUTE(date1, date2)

Checks if two dates are in the same minute.

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if in the same minute, FALSE otherwise.

Examples:

DATEISSAMEMINUTE(2024-05-06T12:30:00Z, 2024-05-06T12:30:45Z) → TRUE
DATEISSAMEMINUTE(2024-05-06T12:30:00Z, 2024-05-06T12:31:00Z) → FALSE

Related: DATEISSAMEHOUR, DATEADDSECONDS

DATEISSAMEMONTH(date1, date2)

Checks if two dates are in the same month.

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if in the same calendar month, FALSE otherwise.

Examples:

DATEISSAMEMONTH(2024-05-06, 2024-05-31) → TRUE
DATEISSAMEMONTH(2024-05-06, 2024-06-06) → FALSE

Related: DATEISSAMEWEEK, DATEISSAMEYEAR

DATEISSAMEWEEK(date1, date2)

Checks if two dates are in the same week. Uses ISO week numbering (Monday through Sunday).

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if in the same ISO week, FALSE otherwise.

Examples:

DATEISSAMEWEEK(2024-05-06, 2024-05-10) → TRUE
DATEISSAMEWEEK(2024-05-06, 2024-05-13) → FALSE

Related: DATEISSAMEMONTH, DATEISSAMEDAY

DATEISSAMEYEAR(date1, date2)

Checks if two dates are in the same year.

Parameters:

  • date1 (date): First date

  • date2 (date): Second date

Returns: boolean. TRUE if in the same calendar year, FALSE otherwise.

Examples:

DATEISSAMEYEAR(2024-05-06, 2024-12-31) → TRUE
DATEISSAMEYEAR(2024-05-06, 2025-05-06) → FALSE

Related: DATEISSAMEMONTH, DATEISSAMEWEEK

DATESUBTRACTDAYS(date, days)

Subtracts a specified number of days from a date.

Parameters:

  • date (date): The starting date

  • days (number): Number of days to subtract

Returns: date. New date after subtracting days.

Examples:

DATESUBTRACTDAYS(2024-05-06, 7) → 2024-04-29
DATESUBTRACTDAYS(2024-05-06, 0) → 2024-05-06

Related: DATEADDDAYS, DATESUBTRACTWEEKS

DATESUBTRACTHOURS(date, hours)

Subtracts a specified number of hours from a date.

Parameters:

  • date (date): The starting date

  • hours (number): Number of hours to subtract

Returns: date. New date after subtracting hours.

Examples:

DATESUBTRACTHOURS(2024-05-06T12:00:00Z, 2) → 2024-05-06T10:00:00Z
DATESUBTRACTHOURS(2024-05-06T12:00:00Z, 15) → 2024-05-05T21:00:00Z

Related: DATEADDHOURS, DATESUBTRACTMINUTES

DATESUBTRACTMINUTES(date, minutes)

Subtracts a specified number of minutes from a date.

Parameters:

  • date (date): The starting date

  • minutes (number): Number of minutes to subtract

Returns: date. New date after subtracting minutes.

Examples:

DATESUBTRACTMINUTES(2024-05-06T12:00:00Z, 30) → 2024-05-06T11:30:00Z
DATESUBTRACTMINUTES(2024-05-06T12:00:00Z, 90) → 2024-05-06T10:30:00Z

Related: DATEADDMINUTES, DATESUBTRACTHOURS

DATESUBTRACTMONTHS(date, months)

Subtracts a specified number of months from a date.

Parameters:

  • date (date): The starting date

  • months (number): Number of months to subtract

Returns: date. New date after subtracting months.

Examples:

DATESUBTRACTMONTHS(2024-05-06, 2) → 2024-03-06
DATESUBTRACTMONTHS(2024-05-06, 12) → 2023-05-06

Related: DATEADDMONTHS, DATESUBTRACTYEARS

DATESUBTRACTSECONDS(date, seconds)

Subtracts a specified number of seconds from a date.

Parameters:

  • date (date): The starting date

  • seconds (number): Number of seconds to subtract

Returns: date. New date after subtracting seconds.

Examples:

DATESUBTRACTSECONDS(2024-05-06T12:00:00Z, 60) → 2024-05-06T11:59:00Z
DATESUBTRACTSECONDS(2024-05-06T12:00:00Z, 3600) → 2024-05-06T11:00:00Z

Related: DATEADDSECONDS, DATESUBTRACTMINUTES

DATESUBTRACTWEEKS(date, weeks)

Subtracts a specified number of weeks from a date.

Parameters:

  • date (date): The starting date

  • weeks (number): Number of weeks to subtract

Returns: date. New date after subtracting weeks.

Examples:

DATESUBTRACTWEEKS(2024-05-06, 2) → 2024-04-22
DATESUBTRACTWEEKS(2024-05-06, 1) → 2024-04-29

Related: DATEADDWEEKS, DATESUBTRACTDAYS

DATESUBTRACTYEARS(date, years)

Subtracts a specified number of years from a date.

Parameters:

  • date (date): The starting date

  • years (number): Number of years to subtract

Returns: date. New date after subtracting years.

Examples:

DATESUBTRACTYEARS(2024-05-06, 1) → 2023-05-06
DATESUBTRACTYEARS(2024-05-06, 5) → 2019-05-06

Related: DATEADDYEARS, DATESUBTRACTMONTHS

DATETOSTRING(date, format)

Converts a date to a string using date-fns format tokens.

Parameters:

  • date (date): The date to format

  • format (string): Format string using date-fns tokens

Common format tokens:

  • yyyy: 4-digit year

  • MM: 2-digit month (01–12)

  • dd: 2-digit day (01–31)

  • HH: 2-digit hour (00–23)

  • mm: 2-digit minute (00–59)

  • ss: 2-digit second (00–59)

Returns: string. Formatted date string.

Examples:

DATETOSTRING(2024-05-06, "yyyy-MM-dd") → "2024-05-06"
DATETOSTRING(2024-05-06T15:30:45Z, "HH:mm:ss") → "15:30:45"
DATETOSTRING(2024-05-06, "MMMM dd, yyyy") → "May 06, 2024"

Related: DATE, DATEADDDAYS

DAY(date)

Returns the day of the week for a date as a number 0–6, where 0 is Sunday.

Parameters:

  • date (date): The date to evaluate

Returns: number. Day of week (0 = Sunday, 1 = Monday, ..., 6 = Saturday).

Examples:

DAY(2024-05-06) → 1 (Monday)
DAY(2024-05-05) → 0 (Sunday)
DAY(2024-05-12) → 0 (Sunday)

Related: MONTH, YEAR, WEEK

HOUR(date)

Returns the hour of the day for a date.

Parameters:

  • date (date): The date to evaluate

Returns: number. Hour of day (0–23).

Examples:

HOUR(2024-05-06T15:30:00Z) → 15
HOUR(2024-05-06T08:00:00Z) → 8
HOUR(2024-05-06T00:00:00Z) → 0

Related: DATEADDHOURS, DATEISSAMEHOUR

ISWEEKDAY(date)

Checks if a date is a weekday (Monday through Friday).

Parameters:

  • date (date): The date to evaluate

Returns: boolean. TRUE if weekday, FALSE if weekend.

Examples:

ISWEEKDAY(2024-05-06) → TRUE (Monday)
ISWEEKDAY(2024-05-05) → FALSE (Sunday)
ISWEEKDAY(2024-05-11) → FALSE (Saturday)

Related: ISWEEKEND, DAY

ISWEEKEND(date)

Checks if a date is a weekend (Saturday or Sunday).

Parameters:

  • date (date): The date to evaluate

Returns: boolean. TRUE if weekend, FALSE if weekday.

Examples:

ISWEEKEND(2024-05-05) → TRUE (Sunday)
ISWEEKEND(2024-05-11) → TRUE (Saturday)
ISWEEKEND(2024-05-06) → FALSE (Monday)

Related: ISWEEKDAY, DAY

MONTH(date)

Returns the month for a date as a number 0–11, where 0 is January.

Parameters:

  • date (date): The date to evaluate

Returns: number. Month (0 = January, 1 = February, ..., 11 = December).

Examples:

MONTH(2024-05-06) → 4 (May)
MONTH(2024-01-15) → 0 (January)
MONTH(2024-12-25) → 11 (December)

Related: DAY, YEAR, DATEISSAMEMONTH

WEEK(date)

Returns the week number for a date using the ISO week numbering system. The first day of the week is Monday.

Parameters:

  • date (date): The date to evaluate

Returns: number. ISO week number (1–53).

Examples:

WEEK(2024-05-06) → 19
WEEK(2024-01-01) → 1
WEEK(2024-12-30) → 1

Related: DAY, MONTH, DATEISSAMEWEEK

YEAR(date)

Returns the year for a date.

Parameters:

  • date (date): The date to evaluate

Returns: number. The year (e.g., 2024).

Examples:

YEAR(2024-05-06) → 2024
YEAR(2025-01-01) → 2025
YEAR(2020-12-31) → 2020

Related: MONTH, DAY, DATEISSAMEYEAR

Logical Functions

Functions for conditional logic and truth values.

IF(condition, value_if_true, value_if_false)

Returns a value based on a condition.

Parameters:

  • condition (boolean): The condition to evaluate

  • value_if_true (any): Value returned if condition is TRUE

  • value_if_false (any): Value returned if condition is FALSE

Returns: any. value_if_true or value_if_false based on the condition.

Examples:

IF(EQUALS(5, 5), "equal", "not equal") → "equal"
IF(GT(10, 5), "yes", "no") → "yes"
IF(DATEISBEFORE(2024-05-06, NOW()), "past", "future") → "past"

Related: EQUALS, NOTEQUALS

Utility Functions

Functions for no-input constants and other utilities.

FALSE()

Returns the logical value FALSE.

Parameters: None.

Returns: boolean. FALSE.

Examples:

FALSE() → FALSE
IF(FALSE(), "yes", "no") → "no"

Related: TRUE, IF

NOW()

Returns the current date and time when the formula is executed.

Parameters: None.

Returns: date. Current date and time in UTC.

Examples:

NOW() → 2024-05-06T14:25:32Z (at execution time)
DATEISBEFORE(NOW(), DATEADDDAYS(NOW(), 1)) → TRUE

Related: DATE, DATEADDDAYS

PI()

Returns the value of PI.

Parameters: None.

Returns: number. Mathematical constant π ≈ 3.14159265359.

Examples:

PI() → 3.14159265359
PRODUCT([PI(), 2]) → 6.28318530718

Related: PRODUCT, AVERAGE

TRUE()

Returns the logical value TRUE.

Parameters: None.

Returns: boolean. TRUE.

Examples:

TRUE() → TRUE
IF(TRUE(), "yes", "no") → "yes"

Related: FALSE, IF

Additional Resources

Up Next

Field Types

Related Video

Create formulas across categories with Layer’s new visual formula editor