Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,243 Replies
5942 Topics
S
Savin_PerrineNew Participant
posted in Formulas
create a table "summary results" of the base

Hello, I would like create a table with summary results of all the base. For exemple, for a base with different tables like : 1 - Suppliers2 - Products3 - Customers 4 - SalesI would like have in a table (to use in a app built with softr) global datas like : - the number of records in the supplier's table or of customers- the average turnover - the total average ...How can i do that ? Thanks 😃 (I know it is possible with blocks "extensions" but it is not possible to have datas blocks in softr ... ) 

21
TheTimeSavingCo
K
KerryHNew Participant
asked in Formulas
Can I create a formula to auto fill Primary Columm in Table 2 from a field in Table 1?

I'm pretty sure I'm over thinking this process so I apologize in advance if this is obvious to most.I have a field in Table 1 which when data is entered, I'd like that data to automatically create a new record in the primary column of Table 2.Table 1 is my ideas bank and when I have created a  "Working Title" (field name in Table 1) for that idea, I'd like the title to be automatically added to Primary Column in Table 2, thus creating a new record.Really hope this makes sense.Any help appreciated.

242
K
D
dpnetworkNew Participant
asked in Formulas
IF Statement syntax

Hello, maybe this is not even possible, but if it is, what would be the proper syntax for this ?IF(VALUE({Total Rate 6})) >0, VALUE({Total Rate 6}), Blank() This field is showing the data of another column called "Total Rate 6", but i want it to be blank if the amount is 0(zero)

65
Ben_Young1
N
n0xpInspiring
asked in Formulas
ARRAYJOIN(ARRAYCOMPACT())

Hi everyone, I'm trying to get this formular to work, and although it parses there's no output.   ARRAYJOIN(ARRAYCOMPACT(Field1,Field2,Field3,Field4), ", ")  The fields are a mix of and link-to-another-record fields lookups (of link-to-another-record fields) where the outputs all are fine. The reason why I'm using an ARRAYCOMPACT as well is because half of the fields are usually empty.Thank you very much ❤️

471
N
W
weoimNew Participant
posted in Formulas
Find function and sequence check logic

Hi guys, I need help building formula automation and seem to have trouble with the Find function.So we have an outreach database for our customers and for each outreach, we track a response as "yes" or "no," Ideally after two constant rejections we would mark the customer inactive. If later on they respond or reach out then we will mark them active again. With this in context, I'm trying to build an automatic status update function in Airtable. If one customer's status is "No, No" or "Yes, No, No," the customer's status shall automatically change to "inactive." If a new yes is recorded, like "No, No, Yes" then it shall change to "active,"I tried a few different ways but none seem to work perfectly. The closest I could think about right now is to use the "Find()" function which in my understanding returns the last position of the string being looked up. However, when I tried it, this is what I got:only one string "yes" or "no" is in the cell, return 1";Two "no" in the cell, Find("n

62
W
Lisa_Wetherby
Lisa_WetherbyParticipating Frequently
asked in Formulas
Calculate dates

I know this is super simple, but I am new to formulas. Here's my problem:I have volunteers signing up for a rotation and I'm trying to make a view that will capture their hotel needs, which are generally: Check In one day BEFORE their First Day BOG and Check Out one day AFTER their Last Day BOG (BOG = Boots on the Ground).This is the formula I am using to get their Check In date: DATEADD({First Day BOG},-1,'days')It is not calculating correctly.This is the formula I am using to get their Check Out date: DATEADD({Last Day BOG},1,'days')Also not calculating correctly. Help & TIA

102
Lisa_Wetherby
J
JaponaisNaturelNew Participant
asked in Formulas
Converting ISO date to European format date with leading zeros

Hi everyone,An application is sending me a date in ISO format (2022-02-13) and I want to convert it in the european date format without dropping the day and month leading zeros ( = 13/02/2022). I managed to convert from ISO to European but the leading zeros are dropped.If I use the DATETIME_FORMAT with the 'L' specifier, the leading zeros are back but the format reverse to the american format ( = MM/DD/YYYY).Is there a way to keep both the european format and the leading month and day zeros ?Thanks in advance for your help, Gilles

152
J
A
asher11New Participant
asked in Formulas
Count of records not adding up correctly

The bottom one should be a count of 2. Team A and Team B are two fields. I tried COUNT, COUNTA, COUNTALLHere's the formulaI can't even add a Count to that specific fieldThanks

243
A
C
CottageKeeperInspiring
asked in Formulas
How to Format a Formula Field that pulls a Currency from another Formula Field

Need the 'Test Formula' field to format the 'Trip Total:' as currency.  Any ideas anyone?

91
C
Cody_Winchester
Cody_WinchesterInspiring
posted in Formulas
Calculate Number of Workdays Per Month

Hello, I'm stuck trying to figure out how many workdays exist in a given month. It is easy enough to get the number of workdays between a start and end date using the WORKDAY_DIFF() function, and it also seems possible to collect the total number of days per month. However, I have not come across a solution to determine the number of workdays per month. Any help would be appreciated!

474
Cody_Winchester
Tom_David
Tom_DavidKnown Participant
asked in Formulas
Help with Formula Syntax Please (Newbie)

Field 1: is a currency fieldField 2: a checkboxField 3: is a currency fieldI need a Formula in field 3 to do this: IF field 2 = 1, copy & paste the value in field 1 to field 3.Thank you.

135
Tom_David
B
blmahonNew Participant
posted in Formulas
Creating If statement formulas with a column that has both date and time

I have content creation table that has a field for date and time.  I am trying to create a formula similar to this  If ( {Date to Post}=TODAY( ), "Post") but it is throwing an error and I wonder if it is because my column has both date and time. this is the youtube video that was helping me.  https://www.youtube.com/watch?v=pai7nmnLh6g But i can't get past entering the formula. Any help would be appreciated.

91
Ben_Young1
bedjik
bedjikKnown Participant
asked in Formulas
Multiple Select Formula for TimeSheet

Hello,    Hope someone will be able to help me, I am trying to create a formula field for my employee to auto calculate his work hours for a day. He fills out the form and I am tired to calculate his hours per day.So, I've got one field "IN" with multiple select options "8:00am" "8:30am" and "9:00am" and another field "OUT" with multiple select hours "3:00pm", "3:30pm" and "4:00pm" (I have more options, but it does not matter here), so ones IN and OUT are filled, I want to have a formula field calculating how many hours my employee worked today. I thing it should be done as logical formula, like if IN is *8:00am" and OUT is "3:00pm" then HOURS result is 7, something like this, but I can not make it right, anybody could help?

176
bedjik
J
jm22New Participant
posted in Formulas
Turn list into a checklist

Hello i have a list of ingredients that are in a single long text cell SaSweet potatoCarrotI want it to put a ▢ infront of each item ▢ Salmon▢ Sweet potato▢ Carrot TIA

662
TheTimeSavingCo
S
SuperhostNew Participant
posted in Formulas
Field Reference Formula Error

I have a formula that works when a set decimal is entered but not when the decimal is inputted from a field reference. The formula that works (with manual decimal at the end): IF({Custom MSH Fee} > 0, {Custom MSH Fee}, ({Total Booking Price}+{Channel Booking Fee Amount (inc manual fees)})*0.1)The formula that doesn't work (with decimal from Field Reference) IF({Custom MSH Fee} > 0,{Custom MSH Fee},({Total Booking Price}+{Channel Booking Fee Amount (inc manual fees)})*{MSH Commission Decimal})What am I doing wrong here? I want to be able to vary the decimal based on the data entered in the reference field. Thanks all.

31
TheTimeSavingCo
Alec11
Alec11Participating Frequently
posted in Formulas
Manipulating Date data into the proper format

Hello,I have a web scraper that is pulling in assorted data from a list of projects. The issue being that the date/time data that it scrapes into my base doesn't convert very well into an actual date field type.Here is what I am working with:Any ideas on how i can remedy this? I need the date field type so i can better filter the projects.Also welcome to any suggestions on how to best handle the "Bids In" column where it gives me varied answers that don't filter very well 😕😕 

01
TheTimeSavingCo
J
jm22New Participant
asked in Formulas
Remove TM from front of recipe

Hi there, I know how to get things off the end of a string but not the start!!! I have recipes that look like this  Easy veggie, lentil and quinoa dahl with pita toasts (KIDS)Greek salad with crispy chickpeas and potatoesMiso eggplant with broccolini and edamameTM Pan-fried salmon with creamy pumpkin milletTM Moroccan spiced beef and lentils with carrot couscous I want to create a field that has all the recipe names - but without the TM at the start.  TIA

82
J
E
Eric_Bryant1Participating Frequently
asked in Formulas
Date Formula Subtracting A day

Hi,Am using a formula I've used in bases before, but it's behaving strangely.  It's subtracting a day off of the returned value.  I have two fields with dates.  Only one of the two will ever have a date in it.  I want the formula to grab the date from whichever field has a date already populated in it (from another table)Here is the formula.  IF({Shoot Date Rollup (From Scenes)}="", {Shoot Date Rollup (from Set List)}, {Shoot Date Rollup (From Scenes)})Example:Shoot Date Rollup (From Scenes) = 7/25/23The formula Returns 7/24/23Same if it's the other field that has data Shoot Date Rollup (from Set List) = 8/10/23Formula field returns 8/9/23Thanks for any help.

200
E
A
AnxiousInspiring
asked in Formulas
Hide record ID not working in form prefill.

I am trying to follow this video tutorial: https://www.youtube.com/watch?v=BkAY7OHEgdcThe formula: 'https://airtable.com/shr12G6XKocbeGi0H'&'?prefill_Doll%20ID='&RECORD_ID()&'&prefill_Doll%20Name='&ENCODE_URL_COMPONENT({Doll Sculpt})&'hide_Doll%20ID=true' However, the part to hide the record ID is not working. This is what happened to the form:As you can see, the "Doll Name" area has the formula to hide the record ID in it. 🤔 🤔 I tried doing it exactly like the video and only having "(Doll Sculpt)", but it said there was an error with the formula and I was forced to use "({Doll Sculpt})". Could this be the issue?I haven't even tested the functionality (I haven't set up the automation) of this yet because I have hit the snag listed above. 😥Thanks for reading!

203
M
M
Mary_Schafrath1New Participant
asked in Formulas
Show results by percentage from survey

Hi there smart people!I'm looking to show the percentage of improvement since the implementation of our new Figma redlining process from our team survey. Right now, 5/8 people say the process has either improved or greatly improved! Is it possible to show this in Airtable's Interface? Either as 5/8 or 63%? Participants will continue to add to this survey in the coming weeks. So, I need a create a formula that will update the Interface accordingly. Please, let me know if anyone has an answer!

154
M
Dominic_Mueller
Dominic_MuellerNew Participant
posted in Formulas
Show/Hide complete row in a table with "single select".

Hello, I am working with Airtable and Softr. Sometimes it would be helpful to show or hide single rows of a table. So I am looking for a way/formula to easily switch on or switch off (mute) each row in a table with "single select". Sort of like enabling or disabling the contents of the row (all columns). -- Of course, the content of the row will always remain and will only be shown or hidden. Is this possible? Many thanks in advance for your answers and suggestions! 🙏

502
Dominic_Mueller
J
jessieeatswellNew Participant
asked in Formulas
Auto-populate Due Dates on work days only

I'm building a table with many due date columns and they all need to be automatically calculated based off of one "publication date." For example one due date might be 21 days before publication. BUT, I always want the due date to fall on a work day. How do I make that happen? And a second, related question: I'd like to have the same sort of setup for one of the tasks but instead of an auto-populated single date, I'd like the formula to return an auto populated 3 week time range. (The weekdays are not important for that one.) 

162
TheTimeSavingCo
R
RoseAmeliaInspiring
asked in Formulas
Utilize Date conditions after creating date token in formula

I have used a formula to create a new date value based on another date cell: The Next Due Date uses the following formula: SWITCH(Frequency,'Weekly', DATETIME_FORMAT(DATEADD({Task Due}, 1, 'week'), 'DD/MM/YYYY'),'Monthly', DATETIME_FORMAT(DATEADD({Task Due}, 1, 'month'), 'DD/MM/YYYY'),'Quarterly', DATETIME_FORMAT(DATEADD({Task Due}, 3, 'month'), 'DD/MM/YYYY')) However,  to set up views and automations I need to be able to use the conditional properties of a date value, and I can not seem to filter on any of the normal date values. Is there a way to filter Next Due Date to be 'within' and 'the past week'? Thank you! 

132
R
Andrew_Carville
Andrew_CarvilleKnown Participant
posted in Formulas
Countif alternative - adding totals in one column based on dropdown status in another?

Hi all, I’m trying to work out a simple countif alternative which adds numbers in a column only based on a dropdown selection in another column. For example, if I have dropdown options based on project status (ie. “Not Started”, “In Progress” and “Complete”), and a simple number column tracking how many apartments are in each project, can I have a third column with a formula counting the number of apartments only in Complete projects, which I can then use to create a dashboard display? Or do I have to create a separate view and sort/filter the data this way. Keen to avoid as I already have quite a few different views set up. Thanks in advance! AC

204
D
K
kumar99New Participant
posted in Formulas
Formula to make bullet list from comma separated text

Can you tell me formula to make bullet list from comma separated text?eg text: "text1, text2".expected output:• text1• text2

261
A

Badge winners

Show all badges
Terms & Conditions