Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

New Formula Field Functions

cancel
Showing results for 
Search instead for 
Did you mean: 
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

There are several requests floating around the forums related to the need/desire for more formula field functions - mostly related to arrays, linked records, or javascript like functionality.

I’m going to create this #feature-requests post with some of the initial ones that I have posted myself, or have seen in the forums, and I will endeavor to keep it updated with new ones that I find or see posted; and, I will even endeavor to cross them off the list as Airtable implements them :winking_face:

To keep the format clean, I’m going to make a section that lists just the function requested and a brief description of functionality - I think the intention for use and the use-cases should be clear by the structure of the function. This will be followed by a section with links to some of the posts I have seen addressing the need/desire for some of these functions.

Airtable users - If you have an idea for a function that is not currently in the list, post it as a reply here and I will try to add it to the list in this OP if the intention is clear and the functionality seems feasible.

@Airtable_Support, @Airtable_Team, @Katherine_Duh, @Howie, @Kasra - please consider :grinning_face_with_big_eyes:

Functions List

LOOKUP(table, field, linked-records-field)
  • Lookup field functionality, but can be evaluated within a formula field
ROLLUP(table, field, ROLLUP-FUNCTION())
  • Rollup field functionality, but can be evaluated within a formula field
ARRAY_DIFF(array 1, array 2)
  • Takes two arrays (or strings of objects with a separator that can easily be converted to an array) - returns {array 3} which contains all objects from {array 1} that do not also exist in {array 2} (ie, subtract {array 2} from {array 1})
ARRAY_EACH(array, variable, FUNCTION())
  • Takes an array and a compatible Function - loops over the array and for each object, saves the object in the variable, and allows the user to use the variable in the chosen, compatible, string function (such as SUBSTITUTE(), or FIND()) or in logical Functions (such as IF(), OR(), AND()); works like a JS for loop
ARRAY_INTERSECT(array 1, array 2, array 3, ...)
  • Takes any number of arrays (or strings of objects with a separator that can easily be converted to an array) - returns an array with only objects that exist in every array provided to the function

Links List

23 Comments
W_Vann_Hall
13 - Mars
13 - Mars

Remember you can essentially write a formula within the rollup aggregation function window; simply use the keyword 'values' to reference the rolled-up field. The one relatively constraining limitation is that you can’t address directly more than one rolled-up value, which presumably your suggested enhancement could.

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Exactly - the ability to reference multiple Rollups within a formula would be nice, but also, I think that to make it compatible with the ARRAY_EACH() function being proposed, you’d have to be able to address the Rollup within the ARRAY_EACH() function itself - I’m not sure it would work if you just referenced a distinct Rollup field. Maybe I’m not thinking properly about that.

Also, I’ve always had trouble with writing formulas in Rollup fields - perhaps I haven’t been thinking about using 'values' in the right way

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

I’ve lost the ability to edit this post, sadly.
(explanation here)

Until (hopefully) I can edit it again, here’s another addition to the list:

Function

Formula Field Version

PROD(value 1, value 2, value 3, etc...)

Rollup Field Version

PROD(values)
  • Takes any number of numerical values and multiplies them together to obtain the product of those values

Reference Link

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

@Airtable,

I just got done writing a concatenation formula to create every date between a User entered {Start Date} and {End Date}, with support for up to 1 year (365 days) difference between start and end… :confounded:

IF(
  AND({Cancelled On},{Cncltn Recorded By}),
  BLANK(),
  DATETIME_FORMAT({Start Date},'l') &
  "," &
  DATETIME_FORMAT({End Date},'l') &
  IF(
    {Length in Days} > 1,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        1,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 2,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        2,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 3,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        3,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 4,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        4,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 5,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        5,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 6,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        6,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 7,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        7,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 8,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        8,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 9,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        9,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 10,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        10,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 11,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        11,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 12,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        12,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 13,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        13,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 14,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        14,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 15,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        15,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 16,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        16,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 17,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        17,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 18,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        18,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 19,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        19,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 20,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        20,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 21,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        21,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 22,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        22,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 23,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        23,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 24,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        24,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 25,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        25,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 26,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        26,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 27,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        27,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 28,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        28,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 29,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        29,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 30,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        30,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 31,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        31,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 32,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        32,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 33,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        33,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 34,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        34,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 35,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        35,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 36,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        36,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 37,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        37,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 38,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        38,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 39,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        39,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 40,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        40,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 41,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        41,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 42,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        42,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 43,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        43,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 44,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        44,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 45,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        45,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 46,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        46,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 47,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        47,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 48,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        48,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 49,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        49,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 50,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        50,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 51,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        51,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 52,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        52,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 53,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        53,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 54,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        54,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 55,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        55,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 56,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        56,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 57,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        57,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 58,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        58,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 59,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        59,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 60,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        60,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 61,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        61,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 62,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        62,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 63,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        63,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 64,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        64,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 65,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        65,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 66,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        66,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 67,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        67,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 68,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        68,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 69,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        69,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 70,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        70,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 71,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        71,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 72,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        72,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 73,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        73,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 74,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        74,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 75,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        75,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 76,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        76,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 77,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        77,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 78,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        78,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 79,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        79,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 80,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        80,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 81,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        81,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 82,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        82,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 83,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        83,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 84,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        84,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 85,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        85,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 86,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        86,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 87,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        87,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 88,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        88,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 89,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        89,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 90,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        90,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 91,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        91,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 92,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        92,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 93,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        93,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 94,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        94,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 95,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        95,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 96,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        96,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 97,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        97,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 98,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        98,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 99,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        99,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 100,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        100,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 101,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        101,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 102,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        102,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 103,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        103,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 104,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        104,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 105,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        105,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 106,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        106,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 107,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        107,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 108,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        108,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 109,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        109,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 110,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        110,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 111,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        111,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 112,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        112,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 113,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        113,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 114,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        114,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 115,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        115,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 116,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        116,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 117,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        117,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 118,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        118,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 119,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        119,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 120,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        120,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 121,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        121,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 122,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        122,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 123,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        123,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 124,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        124,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 125,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        125,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 126,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        126,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 127,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        127,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 128,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        128,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 129,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        129,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 130,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        130,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 131,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        131,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 132,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        132,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 133,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        133,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 134,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        134,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 135,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        135,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 136,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        136,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 137,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        137,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 138,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        138,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 139,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        139,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 140,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        140,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 141,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        141,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 142,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        142,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 143,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        143,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 144,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        144,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 145,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        145,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 146,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        146,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 147,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        147,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 148,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        148,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 149,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        149,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 150,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        150,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 151,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        151,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 152,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        152,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 153,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        153,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 154,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        154,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 155,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        155,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 156,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        156,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 157,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        157,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 158,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        158,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 159,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        159,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 160,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        160,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 161,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        161,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 162,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        162,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 163,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        163,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 164,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        164,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 165,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        165,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 166,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        166,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 167,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        167,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 168,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        168,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 169,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        169,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 170,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        170,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 171,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        171,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 172,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        172,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 173,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        173,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 174,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        174,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 175,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        175,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 176,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        176,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 177,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        177,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 178,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        178,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 179,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        179,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 180,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        180,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 181,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        181,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 182,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        182,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 183,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        183,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 184,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        184,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 185,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        185,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 186,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        186,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 187,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        187,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 188,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        188,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 189,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        189,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 190,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        190,
        'day'
      ),
      'l'
    )
  ) &
  IF(
   ... and on to 364; truncated due to post character limit

fellow forum users, feel free to steal this and reuse if it can help you…

The use case here is a booking system that needs to allow for future reservations of a particular site to be checked against all dates for which that site has existing reservations, to avoid double booking.

I would have killed to have had either one of:

  • a function for checking a date against a span of time, given the start and end of the span; perhaps something like:
DATE_IS_IN(dateToCheck, startOfSpan, endOfSpan)
  • an ITERATOR and VARIABLES of any sort to be able to automate the formula I had to write by hand

Thank you for considering! Blessings :grinning_face_with_smiling_eyes:

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Functions

POP(array)
  • Returns the last value out of an array (the array most likely being a reference to a Rollup field)
SHIFT(array) .... or ... PULL(array)
  • Returns the first value out of an array (the array most likely being a reference to a Rollup field)

Link

Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Function

CURRENCY(number, 'currencySymbol')
  • Takes a number as either integer, decimal, or field reference, and a currency symbol ('$', '€', '£', etc…); returns the number rounded and formatted as currency

Link

Andrew_Enright
9 - Sun
9 - Sun

Adding live conversation rates to a CURRENCY function would be killer.

Kamille_Parks
16 - Uranus
16 - Uranus

I would love some sort of DATE_RANGE({Start Date},{End Date},'unit') function to list all the days, months, years, etc. between two given dates. I have a Vacation Planner base where I’m planning several week-long events across a year, and a function like this would come in handy.

Sean_io
5 - Automation Enthusiast
5 - Automation Enthusiast

Wow. NEED this so badly.

:frowning:

Is there no way to automatically get the value of a linked records field automatically now? An API populates my table at the moment from orders on my online store and I need to look up shipping costs per shipping method on a linked table. Right now I have to manually copy the shipping method value to the linked record field to get the lookup to work. It’s a pain.

Justin_Barrett
18 - Pluto
18 - Pluto

I haven’t yet played with the API, but using Integromat (which uses the API under the hood) to edit Airtable data, I can pass text into a linked record field and it will make the link if it finds a record with a matching name in the primary field. Not sure how much extra work it would take directly with the API to do something similar, but perhaps @Bill.French can offer some insight on this.