Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,247 Replies
5944 Topics
A
aliawill2024New Participant
posted in Formulas
Statute of limitations countdown

Hello, I am setting a legal case tracking base and I am trying to do something where:1: When I input the date of incident, it automatically calculates two years from that date2: I would like to have a table that also has a countdown until we reach the Statute of Limitations. Ideally, it would look something like: Date of Incident (03-11-24), Statute of Limitations (03-11-2026), Days remaining: 365I attempted the Datetime_Diff between the two, but it just gives me the number of days between the Date of injury and SOL, so I think I can omit the Date of Injury and just have a running countdown so that every day when I log in, the day is one less remaining in the column that is counting down. 

31
kuovonne
J
JohnDoeInspiring
asked in Formulas
Using a formula to append strings

Is there a way to append data in a field? Let's say I have a long text field called "Campaigns" which will contain the different campaigns that a customer have agreed to through our emails. Is there a way to preserve the previous text and basically add the new text to the field? 

61
TheTimeSavingCo
M
malloryjaxNew Participant
posted in Formulas
Dates "sheet"? Update records based on one date

I want to create a "sheet" timetable that creates new dates using a formula based on one date that is inputted. I know there's probably a better way to say that so please disregard my ignorance. Here's what I want to happen...I input the "Class Start" date and all other dates are created using formulas. Here is our current sheet.Is this possible in airtable? I imagine there is a work around. 

42
TheTimeSavingCo
B
betteratunoNew Participant
asked in Formulas
Converting partial year and text to year/month

Hello! I have a large set of dates that are a partial year and month, and would like to convert this to a final date of the month format. Examples:Data I HaveDesired Format24-Apr2024-04-3024-Aug2024-08-3023-Dec2023-12-31I've tried concat using LEFT( & RIGHT(I'd like to try SUBSTITUTE, but not sure of the formatting. I'm also open to any suggestionsThanks for your time! 

81
S
G
GGANew Participant
asked in Formulas
Day date glitch

why does a 'day date' formula, based on a 'start time' return a day as the day before, when the start time is before 8am? Here is my formula column which is based on my start day/time fieldDATETIME_FORMAT(Start,"D-ddd") 

80
G
J
jen1392New Participant
asked in Formulas
Return multiple values from all columns which meet criteria

Hi all,I have formulated columns Apple, Orange, Banana, and the formula produces "Y" or "N" values. I want to create another column that will return the name of the column if their value is "Y". Is there any way to do this?I have tried nested IF formula, like IF(Apple="Y", "Apple", Orange="Y", "Orange"...), but the problem is they just return one value, but I want all the columns as long as their value is "Y".Thanks in advance!

186
pressGO_design
L
lmacParticipating Frequently
posted in Formulas
Limit data submission by user

I would like to limit the number of rows created in a table. I am planning to have a limited data entry period with 100 users where each user can only put in 10 rows of data. How would I achieve this with Airtable?

224
TheTimeSavingCo
T
thewhitworkflowNew Participant
posted in Formulas
Rollup Max() Issue for Recent Date

I'm setting up an interface that will serve as a dashboard to show my time overall client financial info. I used rollups so all the data would be in one table which will allow people viewing the dashboard to only use on filter to filter by client. The only problem I'm running in to is I would like to have the interface say "The client was last invoiced on RECENT DATE" I tried using a max() formula in a rollup column. The problem I'm having is that it's returning 2/29 as the most recent date when I have dates from this week, first week of March. How can I make sure it's actually showing the most recent date?

113
TheTimeSavingCo
J
JaclynBParticipating Frequently
asked in Formulas
Add Dates Automatically based on Word

If there are options in a drop down menu (example Quiz or Test), start dates are entered by user, can I add a new column with a formula that detects the word 'quiz' or 'test' in the previous column and add an end date automatically based on the word? A quiz is 3 days after start date and a test is 6 days after start date for example. Appreciate any insight.

377
K
NLOIA
NLOIAInspiring
posted in Formulas
How to return the ealier date and the latest date from two separated columns that was groupped?

Hi !     Coleagues, I have a project in AirTable in a single table with 174 rows and 11 (eleven) categories that I can group these rows.     Each of these 174 tasks has its own "Start Date" and "End Date" column, with date format in it.     I want to know the earllier "Start Date" and the latest "end Date" inside each of these modules, when filtered by them.     (The objective is to show a timeline filtered by these "MODULES", using these fields with this formuila that identifies the earliest and the lates dates... I'm open to other approachs).   I achieve the first step with these two formulas. They returns the date in each of these fields for its row:1 - For the earliest "Start date" it is:MIN( VALUE(DATETIME_FORMAT({Start date}, 'MM/DD/YYYY')) )2 - For the latest "End date" it is:MAX( VALUE(DATETIME_FORMAT({End date}, 'MM/DD/YYYY')) )But !!...These formulas, based in the columns (fields) "start date" and "end

22
NLOIA
H
HannahSKnown Participant
asked in Formulas
Help with a conditional formula

Can anyone tell me where I'm going wrong with this formula? SWITCH( {HS-LA %},"15%", ({GBP Amount}-{HS-LA Commission}),"10%", ({GBP Amount}-{HS-LA Commission}-{HS-LA Commission}),)Or suggest another way to achieve it - I've tried a few IF options too but can't get that to work either. For context, I'm a literary agent and depending on the type of deal I do I either take 15% or 10% commission. Where I take 10% a co-agent will have also taken 10% but that's not necessary for me to track, other than that when I'm working out the author's overall income I need to make sure I know they didn't get it (hence trying to take away my commission twice in those instances here). The closest I got to it working (ie, it actually ran something instead of refusing to save because of an error), I got words back instead of numbers and what I need is numbers. 

82
H
A
amoersenNew Participant
posted in Formulas
Formula to auto generate data in new cell

I am using Air Table to capture costs per garment style and there are 12 versions of the costs that are available to use based on importing tariffs and shipping modes. These 12 costs are hard keyed into the base. I would like to be able to make the choice on a tariff rate and a ship mode and have the cost autopopulate into a new cell so I can link this elsewhere in my base. Is there a formula that could be used? 

31
TheTimeSavingCo
A
alyssaradosNew Participant
asked in Formulas
Separate String of Values by Comma

I am trying to separate the values in the "ADKAR numerical value" column so that the first number (2) is inputted into the "Awareness value" column. Then so on where I input the next number after the comma into another column, etc.Any help is greatly appreciated! 🙂  

141
S
T
Thibault_GontarNew Participant
posted in Formulas
Sum a row containing several values

Hello everyone,I'm trying to sum a row containing several values.In fact, in the second cell with 140120,178216,0,0... I'd like to sum these values.Do you have any ideas?Thanks in advance,

11
TheTimeSavingCo
R
ReDeGarmoNew Participant
posted in Formulas
Linked record counting

Hello,My subject might not be entirely accurate. Bangin my head against a wall for a few days now.  I haven't been able to find the solution to my problem here, and I feel like there really must be one.  I'm trying to come up with a way to quickly calculate what individual contractors should be paid for events, and display as a grid or list or interface that is easy for the person entering "payment requests" to read. This is currently done by coping over the information into an excel spreadsheet to do the calculations and then reentered into a payment request spreadsheet...but I already have all the information here in airtableI have a table of "events", and table of "sessions" (an event might be made up of 1 or more sessions/ days) and a table of "contractors". I assign contractors to "sessions" through linked records. A contractor may work 1, 2, or all "sessions" of an "event". The sessions have a set number of hours assigned to each, and I've got a payment formul

11
TheTimeSavingCo
S
S_Pacheco-WilliNew Participant
asked in Formulas
DateAdd Returning String of Numbers

Hi there! I’m trying to use the IF and DATEADD formulas to calculate a Project Start Date based on whether or not a Launch Date is entered for a record. If there is no Launch Date entered, the record field should be left blank. Here is the formula: IF({LAUNCH DATE} = BLANK(), “”, DATEADD({LAUNCH DATE}, -16,‘week’)) What I am getting is a date string (2021-10-18T00:00:00.000Z) instead of the Friendly Date format. Help please! :slightly_smiling_face:

105
K
S
Sara11Inspiring
asked in Formulas
IF Clause formula based on different table

Hi everyone, I'm really bad with the formula field!I'm trying to create a formula that prints "OK" if each user that I added has an active status; if some users have inactive status, print "not everyone," and if nobody has an active status, print "nobody." My problem is that the formula is based on info from another table, but I need it on that table.Those are my examples of base: I'm trying to add this formula field to the Report Table. Can someone help me? thank you!  

52
S
U
UtahManNew Participant
posted in Formulas
How to use checkbox with nested IF and AND statement

Hello,  I'm trying to create a formula that will return "Check Other Income" if the Contract * .25 > Other income and if Other Income Verified is checked it will return "Income Verified" This is how I have written the formula:IF({Other Income} > (Contract* .25), "Check Other Income", IF(AND({Other Income} > (Contract* .25),{Other Income Verified} = 1) , "Income Verified", "")) No errors returned, I can get it to say "Check Other Income", but the second half of the formula that has to do withe the checkbox is not returning "Income Verified.  Can anyone help me figure out why? Thank you!

41
TheTimeSavingCo
P
pauliusNew Participant
asked in Formulas
How to get Earliest or Latest date from multiple dates in a cell.

I am trying to get Earliest or Latest date from a field (example: 2022-10-13, 2022-10-15). When I use Min or Max formulas - I get zeros. (I marked those areas as red)Looking forward for solutions. Thank you!To better understand my case I have prepared how everything works in my Airtable:First sheet is for a/b test Data. We import all a/b tests information here.Single line textLink to Other sheetSingle line textFormulaFormulaLocaleLink to FlowsA/B Test DatesA/B Test Start DateA/B Test End DateFRCountdown timer10/13/2022 - 01/02/20232022-10-13 2023-01-02DECountdown timer10/15/2022 - 01/05/2023 2022-10-15 2023-01-05 Countdown timer test runs on multiple locales. in this case - French and German locales.I receive it in such formatI use this formula for this:DATESTR(IF({A/B Test Dates}, REGEX_EXTRACT({A/B Test Dates}, "[^ ]*"))) I use this formula for this:DATESTR(IF({A/B Test Dates}, REGEX_EXTRACT({A/B Test Dates}, "[^ ]*$")))Second sheet, which is r

973
NLOIA
D
dylanchernNew Participant
posted in Formulas
Formula that turns a number into itself and all previous numbers/integers???

How do I make a formula that turns a number into itself and all the previous numbers?I.E. the number in the field is 8The formula should turn the field into:1, 2, 3, 4, 5, 6, 7, 8Please help!!! Thank you 🙂

32
Dan_Montoya
R
rohanmehta2000New Participant
posted in Formulas
Use a formula to generate values that are formatted like a multiple select list

I want to create an IF formula that converts a single select to a multiple select.For example, in column 1 I want the value 'Primary Care Physician' to turn into a multiple selection of 'Internal Medicine Physician' and 'Family Medicine Physician'. It seems like the default formula output is a single line text, but is there any way to convert it to a multiple select list so that the data is actually stored in a list format? 

61
TheTimeSavingCo
P
Project_NP_LSKnown Participant
posted in Formulas
Calculate difference between 2 dates and return answer in Years and Days

Hello AT Community, I am trying to write a formula that returns the number of years and days in between two dates. So for example, assuming {DoB} is 18 FEB 1994, DATETIME_DIFF(TODAY(),DoB,'years'),"y ") returns 30 which is correct.I would also like to calculate the number of remaining days i.e. between the last full year (18 FEB 2024 and today). The answer should be 14 however this extract from my Airtable formula (MOD(DATETIME_DIFF(TODAY(),DoB,'days'),365),"d"))) is returning 21.Can anyone tell me what I am doing wrong please?

513
Dan_Montoya
H
hauwiInspiring
asked in Formulas
IF AND BLANK formula not working for attachment field

Hi. I'm struggling with a formula to include a blank attachment field as part of 3 AND conditions. I'm testing this in my base before I put the AND part into a make.com airtable trigger module.At the moment, I have this:  IF( AND( {Ind Doc}!=BLANK(), DATETIME_DIFF( NOW(),{Record Created}, "days" ) >13, DATETIME_DIFF( NOW(),{Record Created}, "days" ) <30), "YES", "NO" ) This works, but ignores the first parameter and returns "YES" even for records that have an attachment in {Ind Doc}(Sorry if syntax for the multi-line formula is incorrect, I'm only just starting to learn to write formulas like this)! 🙂 

304
dilipborad
K_B1
K_B1Known Participant
asked in Formulas
Copy Column with large amount of data

I have a Long Text column in my table with a lot of text, likely 20K plus pages. I used formulas to clean this text, removing undesirable characters, extra spaces, etc.  Is there any way to copy the contents of the Formula column back to the Long Text column, so that the Long Text column will ahve clean but editable data for future use? While I can copy and past some smaller data sets, when I tried to copy and paste this column, I get a 'too large' error.

220
K_B1
A
AmyM11New Participant
asked in Formulas
Formula in primary field To create id: Autonumber-year

I'm super new to airtable and I've been searching for the answer to this and I can't seem to find what I am looking for- likely because I don't know the right terminology but I have a table in my base and I want the primary field to auto populate each new record with a number and the year the record was created so that if there are multiple years I can filter them easily later.I figure it's some sort of formula because I know how to make the field auto number, but I can't figure out the formula to do it. Thanks in advance Amy 

222
A

Badge winners

  • Welcome
    jeckelbargerhas earned the badge Welcome
  • First Topic
    ABTChas earned the badge First Topic
  • Welcome
    ABTChas earned the badge Welcome
  • Welcome
    danielahas earned the badge Welcome
  • First Reply
    AlineWhas earned the badge First Reply
Show all badges
Terms & Conditions