Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,244 Replies
5942 Topics
J
JonathanBInspiring
posted in Formulas
Formulas to display dates in a given range, from which records matching any given date can be searched for

Hi all I’m using a table to create a list of rotas showing who is on duty during a given date range. There will be multiple people on duty across different date ranges in different areas of the country. What I need is then to be able to use a search app in which users can enter any given date, and the results will show who is on duty on that date, and only those. I’ve only got as far as OR(IS_SAME(TODAY(), {Start}), IS_SAME(TODAY(), {Finish})) which gives “1” if {Start} is today’s date or if {Finish} is today’s date, and I think I’ll need the IS_SAME function at some point, but I’m stumped when it comes to getting the whole range of dates in one field. My thinking for how to do this is as follows: Make a formula to show all dates individually that fall between the dates in a {Start} and a {Finish} field, i.e. IF {Start} is 27/11/2020 and {Finish} is 30/11/2020, the formula will display, “27/11/2020, 28/11/2020, 29/11/2020, 30/11/2020”. Call this field {Date range}. Use {Date r

592
L
T
todcroweNew Participant
posted in Formulas
Combining Fields and additional text based on Field Value

I have the following FieldsTemplate Type (options are either TEXT Only or With Weblink)Title LineCopyName of LinkURL When user selects Template Type Text Only, I need a field that will display the full text and contents:{"iconName":"safety","text":"Copy","title":"Title Line"}When a user selects Template Type With Weblink, I need a field that will display the full text and contents:{"title":"Title Line","text":"Copy","externalURLdisplayName":"Name of Link","externalURL":"URL","iconName":"safety"} 

61
S
M
micha281sthInspiring
asked in Formulas
How to combine fields based on the value of another field ?

Hello, I am looking for a way to combine five fields (address, second address line, zip code and city) to a new multiline text field when another field "second zip code" has a value. So here is an example of the five fields: Address: 220 Miller AvenueSecond Address line: Apartement 50Zip Code: 22033City: ParisCountry: FranceThis values should be combined to the multiline text field "complete address" (with a line break "\n" after each line, except between the zip code and city): 220 Miller AvenueApartement 5022033 ParisFranceBut the six fields should only be combined if the field "second zip code" has a value (i.e. 22033). Is this possible somehow with a formula field?Thanks for help, Michael

72
M
K
kanyewest7New Participant
posted in Formulas
URL for linking to a particular interface page in the record review format

Hello everyone! I'm attempting to construct email and Slack automation to remind my team to update specific projects (records) in a project management interface. When we were in data mode, I merely included a link to the Record URL in the automation message body. Is there a method to refer to a specific project or record in the formatting of the record review? Or in the format of the record summary?I sent the following video to customer support. They suggested I post here since I have not heard back from them.

282
S
J
JPENew Participant
asked in Formulas
concatenate formula issue

Hi everybody I am new to airtable and trying to put a class scheduling together. I have mostly been using the template and learning through trial and error. Now I have produced a weird error that does not seem to impact the functionality but is very annoying.I am using the formulaCONCATENATE(Course, " | ",{Instructor})and it gives me the correct result (e.g. 100:Contract Essentials | Kevin)but all results are now between "" (e.g. "100:Contract Essentials | Kevin")where previously the resulting line did not display ""So now I have a number of rows without "" and all new data has the ""Any idea what I should do? any help would be appreciated

162
J
J
jessicasperoliNew Participant
posted in Formulas
Markdown code showing in email body for some recipients

Hi all,I'm sending out emails via Airtable and some (but not all) recipients are noticing a very weird problem. The URL code shows the markdown text instead of the URL link, ie., it shows [TEXT](url) instead of TEXT.I thought it might just be a problem with the users who use the browser-based version of Outlook, however one of my Gmail users also had a problem with the formatting. Does anyone know how to fix this? Thanks!

113
J
T
tsallisInspiring
asked in Formulas
Extract info from a Long Text field

Hi, I have a long text field with the info bellow and would like to extract the infos in bold to different field but cant find a formula to help me with. Thanks in advance for any help guys.This is the text I have in the long text field (its in portuguese but the info I need is in bold and I inserted a number so it is easier to find:Mapa AstralOi, estou muito feliz em poder realizar a leitura do seu mapa. Reserve pelo menos uma hora para estarmos juntos falando sobre você.Se precisar me chama no wpp 21 96847-4444Local: Esta é uma conferência online via Google Meet.Você pode participar desta reunião pelo seu computador, tablet ou smartphone.https://calendly.com/events/59842209-2613-4829-863c-2da5fe919189/google_meet       (1)Preciso saber seus dados: Data: 03/05/1986 (2)Hora exata de nascimento: 03:10 (3)Cidade Natal: Rio de janeiro (4)Cidade Atual: Rio de janeiro (5)Qual mapa faremos? : Combo Natal+Revolução (R$520,00) (6)Precisa efetuar alterações nesse evento?Canc

842
T
M
Meagan_CaesarKnown Participant
posted in Formulas
Formula to calculate due date formatted so Airtable recognises as a date

Hi, struggling to wrap my head around date formulas, thanks for your patience with all my questions!I'm trying to set up a formula field that will calculate the next due date for a submission based on when a record was submitted via a form. I have the 'created' field set up, which has a date and time, then the formula field, which I'd like to only show the date, not the time. I need Airtable to recognise this field as a date field for use in Automations, but I also want it to show as a recognisable date to the Author viewing the record and receiving the automated email notification.This is what I've tried to get to work, but it still comes out as a string when using DATETIME_PARSE, but when only using DATETIME_FORMAT the field won't recognise it as a date. What am I doing wrong? IF({Type of Edits}='Bio draft edits for Author revision', DATETIME_PARSE(DATETIME_FORMAT(DATEADD({Submitted},1,'week'),"DD/MM/YYYY"), "DD/MM/YYYY")," ")&nb

683
TheTimeSavingCo
A
Andrew_DaviesInspiring
asked in Formulas
Formula or automation to add one image to an attachment field

HelloWondering if someone can help me with something I am struggling with - but thought would be easy!I have an attachment field in one of my tables, and most records have upwards of 10 images.  I'd like to set up a sync'd base which only shows the first image in the array.I created a second attachment field called - "Main image" - but for the life of me, I can't work out how to automatically populate that field, with just the first image from the attachment field.Any ideas?Many thanks,Andrew

732
A
J
Jon11Known Participant
asked in Formulas
Date Span Crossover Formula/Script?

HiIf anyone has any ideas about this I would be very grateful - I can't quite see a way through this one!One of the elements of the system I'm building schedules the use of specific tools. I would like to be automatically notified if a tool is double-scheduled, ie. if the duration of a booking clashes with another booking.As far as I'm aware, there isn't a way of defining timespans in terms of dates in Airtable, other than visually in a timeline or gantt view. The furthest I've got towards a solution is to work with start dates and durations, but I feel like this will get pretty complicated, and would need to include a complex set of IF() formulae that I can't quite fathom out.If anyone can see a clear way through, or just has any logical ways to progress, I would be very grateful.I'm also open to scripting solutions if that's the cleanest way forward.Jon

348
J
M
Michael_HjortenInspiring
asked in Formulas
IF Formula assistance

Question on the following formula.  I have tried just about anything and everything to get this formula field to only calculate the score based on after the date.  The formula works perfectly fine without using any date fields. Form type (single choice option), LH_GC_Def_1 (checkbox) field.   Current working formula for field GC#IF({Form Type}="QCLH",IF(LH_GC_Def_1=1,0,3))+IF({Form Type}="VOICE",IF(LH_GC_Def_1=1,0,2))+IF({Form Type}="QCLH",IF(LH_GC_Def_2=1,0,3))+IF({Form Type}="VOICE",IF(LH_GC_Def_2=1,0,2)) If I try to add in the date field to the GC# column (date of session field) using an operator, it looks like some portions are scored correctly and some are completely wrong, like it is only counting a portion. I have also tried to use IS_AFTER but the formula fails to work, or pulls up an ERROR!.  Any thoughts?

205
S
H
hallo_michaelNew Participant
posted in Formulas
Link to another record across multiple tables

Hello community! We use airtable as an ATS (applicant tracking system) and now would like to extend it to project management.I have 2 different tables: candidates and jobs. I now want to create a new task table (classical kanban project management) where I can link to either a record in the candidate OR in the jobs table. As the "link to another record" only lets me refer to one other table, I am wondering if there is a way to link to a record in +2 tables.Any help is appreciated!Michael

102
H
S
SelimafifiParticipating Frequently
posted in Formulas
Primary Field as a joint array

is there a way of adding a joint array, concatenate to my primary field (containing Email and Full Name), but still be able to link to that table using email only. in other words, my contacts have different emails, and currently primary field is their email. but when i want to link them in other tables, it becomes difficult to find, as you dont always remember their email, so wanted to change primary field to also include full name, but then if i do that, my other automations that use the primary field to sync between tables won't work, as these were based off the email address only, and adding a name to it, changes the value obv. 

111
S
S
Scribbles_New Participant
asked in Formulas
How to Format Dates in a Rollup field with multiple dates

Hi everyone,I've been searching airtable forums for hours yet I can't find a solution that clearly explains what I need to do to achieve my desired results. There are different answers talking about using MIN() or something other formula, but I can't get it to work for me.I have a lookup field that pulls date entries from another table, but there are many duplicate entries I can't remove.So I used ARRAYJOIN(ARRAYUNIQUE(values), '\n') in the column beside it to rollup the fields and finally remove the duplicate entries. The problem is there are times attached to all entries as "T00:00:00.000Z" and all I want is the date part. For the life of me I can' figure out a way to get rid of them. Is there a way to achieve this result somehow? or to Format the dates?Any help explaining what I need to do exactly would be helpful, thank you.

1502
S
A
Addie_DunhamNew Participant
asked in Formulas
IF contains nested formula help

We have a system for reviewing new tools where they are checked for meeting accessibility standards and security standards. These are two separate teams with different workflows. The integration team needs to know when a tool review is considered complete.I have two columns; one for the accessibility phase, one for security phase. I'm trying to make a formula for overall review status. There are multiple decision statuses for each. I would like to make the formula display either "Awaiting Decision" or "Review Complete". Review Complete would show if both columns have a value that indicates a decision was made.For example, for tool A, the accessibility status = Testing Not Required and security status = Complete, it should be marked Review Completed in the formula column.For tool B, the accessibility status = Completed and security status = Intake, it should be marked as "Awaiting Decision".I've tried a variety of IF(FIND( arrangements but I can't seem to get it right. I only saved the

203
A
E
ed018791New Participant
posted in Formulas
New field as a lesson and counting in the records

How to calculate the counts' fields if it is newly created in existing records that we have a formula for the attendance sheet.

51
S
G
Giora_GioraParticipating Frequently
asked in Formulas
Add a Formula Field to See the Newest Item Added to a Rollup Field

My table has a Rollup field (let's call it 'Titles Field').I used the ARRAYJOIN(values, ', '), so the titles are separated by commas.However, I want to add a Formula Field that show only the Newest/Latest title added to the rollup field.Please advise.

122
TheTimeSavingCo
H
harrymccrackenNew Participant
posted in Formulas
Turning <p> and </p> into line breaks in a field

Hello, all. I am importing a CSV file into Airtable. Some of the CSV data ends up as Long Text fields in Airtable, and consists of blocks of text with multiple paragraphs. It comes in with <p> and </p> indicating these paragraphs.In Airtable, the <p> and </p> are preserved and the text is just one giant block with no breaks. What I would like to do is process these files to restore the line breaks as they're intended. I tried doing a search and replace to replace the HTML tapes with \n, but it didn't work. I'm not sure if I understand why, but I am guessing that \n is useful in the case of multiple fields being strung together with line breaks, not a single field that needs to have its line breaks preserved.If anyone has any ideas on how to accomplish what I need--preserving line breaks in fields brought in via CSV--I would appreciate it, Thanks!

131
TheTimeSavingCo
Tiaan_Terblanch
Tiaan_TerblanchInspiring
posted in Formulas
Deducted credit note amount from invoice amount based on document type

Good Day,We added a CSV file with all our invoice amounts, and we have also added a CSV with all our credit notes.Is there a formula that will automatically deduct the credit note amount from the invoice amount based on the Document Type column?So I need a formula that will automatically deduct the credit note amount from the invoice amount based on the document type that they are.Screenshots are attached to show what it looks like when we import it. 

243
Tiaan_Terblanch
A
avyshnyaNew Participant
posted in Formulas
how to use the value of an entry in one table as a parameter for further calculations in other table

The problem is how to use the value of an entry in one table as a parameter for further calculations in other tables.Table "Settings"The Settings table has either (A) a separate record for each parameter, or (B) one record in which several parameters are stored (as separate columns). This is not essential, but the (B) option is better. I need one single select field with "EN", "ES", and "PL" values.Table "Translations"The following table contains translations into different languages: English, Spanish, and Polish of some fields. Let's call it Class, Item, Description. So here are the columns:EN_Class, EN_Item, EN_Description, ES_Class, ES_Item, ES_Description, PL_Class, PL_Item, PL_Description.Table "Base"And the 3rd table Base where the fields Class, Item, and Description contain values from Table "Translations" depending on which language is selected in the settings.Also, I think that storing languages in this way is not the best solution. Would be happy to get any help with this.Lin

102
A
M
Meagan_CaesarKnown Participant
asked in Formulas
Adding formula fields for recurring dates, including SET_TIMEZONE

Hi, I'm sure the answer to this is really simple and I'm just missing it, but could someone please take a look at this formula and let me know why some records would be throwing an ERROR, but others are working? I'm thinking I've got something in the wrong order maybe?We have a group events calendar set up and most events are one-off events, but we have a couple of recurring ones that I'd like to set up an Automation for to automatically generate the next event.This is the formula I'm using:DATEADD(DATETIME_FORMAT(SET_TIMEZONE({Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),1, 'weeks') Also, if I have a checkbox field that is ticked for 'Recurring Events,' how could I add an IF statement to check if that box is ticked first before generating the date, otherwise to leave the field blank? I've tried playing around with it, but I just keep getting errors, so it's time to ask all you amazing formula experts for some help.Thank you!

316
M
B
Brian_MoranNew Participant
asked in Formulas
Extract 0-1000 number from a url after a pattern

From the below URL, I am attempting to extract the number that comes after the pattern "page%5Bnumber%5D="https://api.leadfeeder.com/accounts/133259/leads?end_date=2023-01-01&page%5Bnumber%5D=39&page%5Bsize%5D=1&start_date=2023-01-01In this case, the result would be 39Some help on the formula would be greatly appreciated! Cheers,Brian

72
B
C
ClarenceKNew Participant
asked in Formulas
Formula that shows "Yes" if date is in the previous year

Hello everyone, I have currently the following formula in my table that shows "Yes" if the date in "Fin contrat" is in a future month. I am trying, but with no success, to adapt this formula to show "Yes" in another column if the same date is in the previous year (so currently 2022).Can someone please help me achieve this?Thanks! 🙂 IF( IS_AFTER( {Fin contrat}, DATEADD( DATEADD( DATETIME_PARSE( "01 " & DATETIME_FORMAT( TODAY(), "MM YYYY" ), "DD MM YYYY" ), "1", "months" ), "-1", "days" )),"Yes","No") 

262
C
L
lucas__marParticipating Frequently
asked in Formulas
IF AND for Status Change

Hello, I'm trying to create a Formula that will change Status according to the fields "Start Date" & "End Date". If Today > End Date = "Delayed"If Today < End Date = "In Progress"If Today < Start Date = "Planning"I have written the following formula that works for "Delayed", but shows "True" instead of "In Progress" or "Planning"IF(TODAY() > {End Date},"Delayed", AND(IF(TODAY() < {End Date},"In Progress"),AND(IF(TODAY() < {Start Date},"Planning")))) Anyway can identify my mistake? Thank you!!!

85
L
I
Imee_LeeKnown Participant
posted in Formulas
Automate tick box and Your result type is not a number or a date.

Hello,So currently, we do have a tick box on this view and manually tick it once it is the latest valuation.Do you have any ideas on how can I automate this? without doing the manual ticking?Another issue, I created this formula : IF({latestVal Date} = {Val Date},{Valuation Amount}," ") But it's giving me this outputI know that this could be done when you format it through currency but I'm having this error  "Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date." So what I've done is update the formula to  IF({latestVal Date} = {Val Date},{Valuation Amount},0) and it's giving me this result   The main goal of this formula is to not include those records that are blank or zeros. Is there any missing from the formula that I have set up? Thanks

45
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions