Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,240 Replies
5941 Topics
S
Stephen_JoslynParticipating Frequently
posted in Formulas
Regex Match or Extract - Similar string/phrases not working, works on regex101

I’m trying to extract variable but similar strings of text. They all start with “PARVOVIRUS” and end with “=”. When I build this on regex101.com it works fine. I can extract it multiple times from a long list: Expression: (?>PARVOVIRUS).*?(?=1) However, when I put this back into Airtable I get #ERROR! REGEX_EXTRACT({XmlData}, ‘(?>PARVOVIRUS).*?(?=1)’) The similar strings are: “CANINE PARVOVIRUS IFA SEROLOGY TITRE: =1:20” AND “CANINE PARVOVIRUS IFA SEROLOGY TITRE: >=1:80” I can’t work out what I am doing wrong, and why airtable rejects it. Ultimately I am just trying to extract that 1:20 or 1:80 value but it’s variable length if “&gt” >= is there or not in the text.

163
S
E
E_J1New Participant
posted in Formulas
Counting records in a table - without a common link

Hello, I have a seemingly simple question that I’m struggling to solve. I want to count records meeting certain criteria - for example, how many records generated within the current month. (the end goal is for records to have an autogenerated field that is year.month.sequentialnumber – the year and month are easy, the sequential bit should be a simple matter of # existing records + 1…) If all those records shared a common link to another table, I know I could use that to generate a conditional “count all” rollup field. But it seems totally redundant to make a separate table, with a single record, and link all my original records to that, JUST to obtain my number. Isn’t there any other way? NOTE: so far my Airtable usage has not required me to learn scripting. Very open to it, if that’s an option, but - go easy on me…

352
S
S
Systr4nNew Participant
posted in Formulas
Trigonometry Formula Script

GreetingsI wanted to make a formula as follows:X= {X1}+{distance}*sin({alpha})*cos({beta})I think there are no trig functions in airtable except in scripting.Can anybody help me with the coding of such a script? Thank you

452
Karlstens
L
Leslie_BurkeInspiring
posted in Formulas
How to sum a string of comma-separated dollar values

I am bringing in refund data from Shopify via an automation with webhook. When more than one item is returned, the webhook sends all of the subtotal values in a comma-separated list (see screenshot below). I’m trying to put this into a currency field in an automation, but that is causing the automation to fail when there is more than one item in the list. If I change the field type to single line text, the automation will work, but then I have to find a way to sum these values in a different field. The list of values could be one or many (up to 5+). Do you know of a formula that can accomplish summing the values in the string or do I have to have a script? Thanks for all the help in advance!

262
L
T
Tuesday8093New Participant
posted in Formulas
Partial search for any of multi-select items in string

Hi there, I'm trying to rank titles based on a multi-select. This is my setup:title = string field containing title of a contactprio1_titles = multi-select field where I'm specifying which a preferred contact should have includedtitle_rank = the formula I'm trying to create – fine if it gives out '1' for nowWhat I'm trying to achieve:If the title of my contact contains any of the multi-select words I want the formula to return true.I managed to search for the title in prio1_titles but this is not suitable for partial matches. For example if a title contains "co-founder and coo" I still want the formula to return true because of the partial match for "founder".Would appreciate any help with how to set up this formula.I'd consider myself an experienced no-coder, have no experience with airtable scripts so far, so I would prefer a formula solution.Thanks! 

60
T
Otto_Automator
Otto_AutomatorNew Participant
asked in Formulas
Formula for Name or ID of Current Table

Greetings! There is a formula for getting the ID of the current table record:RECORD_ID()Is there a formula or mechanism for deriving the name or ID of the current table? Such as:TABLE_ID()or…TABLE_NAME()Thank you!

1410
Otto_Automator
M
Max_MealingNew Participant
posted in Formulas
If Statement Nesting Error

This is not working for me - can anybody help correct? IF({Count (Purchase Contracts Signed)} < 0 ; >=1399999,0, IF({Count (Purchase Contracts Signed)} <= 14000000 ; >=2399999, 0.007, IF({Count (Purchase Contracts Signed)} <= 24000000; >=3299999, 0.01, IF({Count (Purchase Contracts Signed)} <= 33000000, 0.013 ) ) ) )

41
Ron_Daniel
S
Sara_DavisNew Participant
posted in Formulas
Sum of invoices paid between two dates

Hi all - I'm trying to set up some budgeting and forecasting tools for my CEO. I've downloaded the "Small business budget" template, which helps, but he's got a preferred way of forecasting that doesn't quite correspond to anything simple.Essentially, he's looking at when funds hit the business' bank account, not when the client pays the invoice.I have invoice information and payments coming into my "income" table. I have my "Forecasts & Actuals" table with dates calculated there to show me the range of when payments coming in would hit that month.For example - for January 2023 income, he'd be looking at payments between December 28th and January 27th instead of January 1 - 31. I've created columns with formulas that give me the "first date of payments" and "last date of payments" for each month.I'm struggling with calculating the rollup of the sum of invoices paid between these two dates, as marked on the "Income" table.  Thank you!

31
TheTimeSavingCo
F
Felix1877New Participant
posted in Formulas
Need a formula for copying only a certain part of the text

Hello community, pls help me with a simple formula The problem is this - you need to insert in a new field text link from the first column, but if there is something extra like "? sort=new" at the end or something similar but ALWAYS begins with a "?", then it must be removed, here is an example

31
F
Nedra_Hines
Nedra_HinesInspiring
asked in Formulas
Modify formula

Hello everyone! I have a formula (below) that calculates a Monthly Rental Fee Renewal Date that is 45 days out from the Staging Date. For one client, I want her date to be calculated at 90 days out.  How can I do that? I know I need an identifier field and I think Client Name is it (file attached). Thanks everyone!IF({Total Monthly Rental Fee w/Tax}>0,DATEADD({Staging Date},46,'days'))

160
A
B
B_Ph_n_Nhan_SNew Participant
posted in Formulas
Looking for a formula that shows value of records are "duplicate" or "not duplicate"

Hi All, I am looking for a formula that shows value of records are “duplicate” or “not duplicate”. In Google Sheet, I use formula (=IF(COUNTIF(A:B,B1)>1,“duplicate”,“not duplicate”) but in Airtable, I can’t use same that formula. Please help me, thanks All.

64
S
A
Alex_DonegaNew Participant
posted in Formulas
How to create Formula IF rule for dates, when Status is DIFFERENT [free(30days) and pay(365 days)]

I have a software and I have a 30-day and paid 365-day trial plan.I need 3 columns:- start date- status30 days365 dayswhen it ends (formula)I need to do the following:I put the start date, for example 01/01/2023status 30 daysautomatically in the column when it ends will be 02/01/2023and if I change the status to 365 days, automatically the field when it ends changes to 01/01/2024this is without ifI'm trying this and i need to put another status and dates tks to help    

82
A
E
Erica_HolleranNew Participant
posted in Formulas
Help with nested if

I can not get this formula to work.... is it completely wrong or am I missing parathesis?  IF({PMID}=“BathJR”,"Jason R",IF({PMID}=”BathSS”,“Stuart S",IF({PMID}=”BathBL”,“Brian L",IF({PMID}=”ODLBL”,“Brandon C",IF({PMID}=”WINBL”,“Evan V",IF({PMID}=”ODLJR,“Brandon C",IF({PMID}=”WINJR”,“Evan V"))))

33
pressGO_design
P
philraymondNew Participant
posted in Formulas
I can add/subtract, but multiply/divide gives error

Is it a bug? My formula works when I do +1.But when I do *1 I get all errors: 

61
P
K
KrysNew Participant
asked in Formulas
How can I automate/script/formula a duplication and editing process?

Hi Airtable community,Attached photos show problem and desired solution separated by the empty row. One example in Airtable. Second example in Google Sheets. This is a small sample of a much larger dataset (hence the query).If Column-B contains 2> (2 or more) names/items then I want that row to duplicate with separate unique Column-B names/items.Your formula/script/automation and how to implement would be greatly appreciated and helpful.  Thank you,Krys

61
Alexey_Gusev
L
LilyUnderNew Participant
posted in Formulas
WORKDAY_DIFF (Completed Early)

Hello,I am trying to get this formula to work and it is currently working for tasks completed on time or late and showing the correct # of days in between, but for tasks completed ahead of the expected finalization date it is not working and that is a mathematical issue as the "-1" I added in my formula to calculate the in between dates is adding to the number of days when a task is completed earlier.  How can I solve this issue?  Any ideas on a formula I can add so it understands that for tasks completed earlier (negative #) the -1 is not being added?WORKDAY_DIFF({Expected Finalization Date},{Actual Finalization Date})-1

132
L
Mark_Newton
Mark_NewtonKnown Participant
posted in Formulas
convert currency to string while preserving format

Hi all,If I have a number field formatted as currency, and I convert it to a string with a formula like this:{unit price}&""it drops all non-zero decimal places as well as the dollar sign, so $7.00 becomes 7. Is there any way to convert to a string with the 2-decimal format intact?

592
Mark_Newton
P
philraymondNew Participant
posted in Formulas
Can I do simple math?

In Google Sheets, I do this in a cell:=1+1...and the answer is output in the cell.Can I do that in AirTable? To be clear, I want to have different formulas in each cell, not one formula for the whole column.

203
P
M
Madeleine_WalthParticipating Frequently
posted in Formulas
Teaching formulas

Hey guys, I don’t know if this is a strange request but I hope you can help me with it. I dived into Airtable for the past months. I really love it and I’m working almost on a daily base on my Airtable base. Now I have set up a quite complex base for my company but I’m lacking of a deeper understanding of formulas. I did a quite complete course and read through everything I could find on the Airtable web itself, but I still feel as lost with formulas as with math at school :grinning_face_with_sweat: I really want to understand how to build formulas and solve the problems and hurdles I have in my base. Therefore I’m looking for a teacher that goes through my Airtable with me and explains me with the given examples how to create different formulas. Of course I will pay for it. If somebody is interested, please let me know, Thanks in advance. Best, Mad

143
M
K
Kosta_KondratenInspiring
asked in Formulas
Filter for multiple concurrent Voicemails

I have a Lookup field called 'Call Result' in my Contact table for my Touchpoints table's 'Call Result' column - which is a drop down. I have a view called 'Hit Lister' and I would like to filter records where I have encountered - for example - 8 voicemails in a row. Here is an example of the contents of one such 'Call Result' lookup row record:Call Me Back, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, VoicemailThe challenge I have is when I add the condition:Call Result does not containVoicemail, Voicemail, Voicemail, VoicemailThe record is not filtered as it should be.Please provide ideas or troubleshooting steps on how I can fix my problem. For reference I have supplied a link to a video below of my cahllenge which should clarify any lasting confusion:https://share.getcloudapp.com/z8ulmjwd

70
K
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
If formula question - help!

Hello- Any help with if CAMPAIGN THEME is blank, the “-” will not show in the formula? IF( {CAMPAIGN NAME}, CONCATENATE( IF( {CAMPAIGN BRAND}, {CAMPAIGN BRAND} & IF(OR({CAMPAIGN NAME}, {CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), " - ", “”) ), IF( {CAMPAIGN NAME}, {CAMPAIGN NAME} & IF(OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), ": ", “”) ), IF( {CAMPAIGN TYPE}, {CAMPAIGN TYPE} & IF(OR({CAMPAIGN THEME}, {EVENT NAME}), " - ", “”) ), IF( {CAMPAIGN THEME}, {CAMPAIGN THEME}& IF(OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), " - ", “”) ), IF({EVENT NAME}, {EVENT NAME}) ), ‘EVENT’ & ’ - ’ & {EVENT NAME} ) A - B: HOLIDAY PROMOTION - HALLOWEEN - The theme is Halloween. Thanks!

31
Ben_Young1
V
vel4New Participant
posted in Formulas
Help with formula

I'm new to Airtable and as expected I need help with a formula, please! This is what I need: IF( {Days to Deliver} is equal to 0 then it   'Expires Today',IF({Days to Deliver}  is equal or bigger than 1 but less or equal than 7 then it is  'About to Expire',IF(  {Days to Deliver} greater than 8 then it is 'On Time'IF(  {Days to Deliver} is <0 then it is'Completed')))) Any help will be appreciated

51
Ben_Young1
T
tishNew Participant
asked in Formulas
A unique formula for each record

I would like to be able to have a different formula in each record, rather than one formula applied to every record.My table is a task list. The Due Date of each task (record) is relative to the Due Date (date field) of the Final Task. Task A is due 1 day before the Final Task is due, Task B is due 5 days before the Final Task is due, etc. Is there a way to input a formula into each record's field that would calculate from a field in another record within the same table? Like if I put "-1 day" into the field (I don't know what type of field could do this) of Task A, it would display October 30, 2023 if Final Task's Due Date was October 31, 2023.The best I've come up with so far is a short text field where I write "1 day before" "5 days before" etc and then plug in the dates in the date field once I've decided Final Task's Due Date. However, this means if I change Final Task's Due Date, I have to manually change all records' Due Dates.I can't find anything like this anywhere. It's

170
T
A
alexandrachapNew Participant
asked in Formulas
Beginner question - Add date condition to a formula

HiI'm looking to create a new field or to modify an existent one.As of 2022, this formula was okay but in 2023, the value for H sould now be 45.94 instead of 39.27 .Because we don't want it to have an impact on our reports and on our Page Designer, we need to add a condition that if the event took place in 2022, H = 39.27 and if the event took place after december 31th 2022, H= 45.94. Could someone help me? I've been trying different things but it seems that I always forget something. Thanks a lot ! 

80
Ron_Daniel
L
Levande_VideoParticipating Frequently
posted in Formulas
How do I make a comma separated string list of numbers into array that is summable

Visually it displays the exact same as if I use a lookup field, but trying to sum it just returns 0 :confused:

19812
J

Badge winners

Show all badges
Terms & Conditions