Formulas

Hi Everyone, I just can’t figure the formula thing out and I’ve spent hours on this, so am hoping that someone can help. Please if you respond - know that I’m not a programmer or formula savvy. I’m struggling with this. I have a column called Follow-Up Date. I create a column called Task Status. I want to do the following, so the Task Status column populates with Planning, Due Soon, Wrap it up, Due Today and Overdue based on date criteria of the Follow-Up date. If Follow-up Date is 2 weeks from the follow-up date based on today’s date, then I want the field to show “Planning” If Follow-Up date is 1 week from the follow-up date based on today’s date, then I want the field to show “Due Soon” If Follow-up date is 3 days from the follow-up date based on today’s date, then I want the field to show “Wrap it up” If Follow-Up date is today, then I want the field to show “Due Today” If Follow-Up date is past the date, then I want the field to show “Overdue” I"ve played with many m

I’ve tried copying and adapting examples from this forum (many thanks all to those who contribute!) and I’ve tried writing formulas from scratch… nothing seems to work! I’m using a very simple base to record my daily step count. I have a field called “Steps” and I want to include a quick visual indication of what my step count is for the day (actually, I’ll be using icons, but it’s simpler to include words in my example). IF( {Steps}>19999, “Yay”, IF( {Steps}>9999, “OK”, IF( {Steps}>1, “No comment” ) ) )

I would like to create a formula to search 1 field and return the search results comma separated. This formula only returns the last item found not all of them. IF(FIND(“text1”,{field}),“text1”,"")&IF(FIND(“text2”,{field}),“text2”,"") Ideally, this would return: text1text2

I am trying to make a formula that will create a status after a certain # of days has passed Scenario Client Makes Appointment Appointment date is added to the record The field called “Client Status” is changed to “Evaluating Proposal” What I need: IF: 7 days later the “Client Status” is still “Evaluating Proposal” then change the formula field to say “Follow Up Email OverDue”

Greeting Airtable Community! I have an existing formula that is returning the correct results: IF(AND({Guest Code}=‘DONOR’,{Status S2}=‘Yes’),First&" "&Last) I now need to modify the formula to include {Status S2}=‘Yes FUP’ and the ‘Yes’ variable. Any assistance on this would be greatly appreciated, as always. John

I am trying to create a formula field result where if the box for “LMI” is not checked, AND someone has “Home Performance” in the multiple select field of things they are interested in, the words “enroll in Comfort Home” appear. Logically I think I know how to do this, having read lots of these help posts… but I keep getting syntax errors. HELP! What am I doing wrong? IF( AND( FIND( “Home Performance”, {Interested in} ), FIND( “0”, {LMI} ) ) "enroll in Comfort Home", " " )

Hi all, I’d appreciate help on this if at all possible? I am trying to write a formula which counts down from a date field called ‘Last Service’ by a number of days as stipulated by a field called ‘Service Days’. Service Days is an integer which describes the number of days between each service activity. i.e. ‘14’ would indicate that there is a service every 14 days. So if the ‘Last service’ was on 01/01/2020 (DD/MM/YY), and the ‘Service Days’ was 14, on 10/01/2020 the new formula would calculate ‘4’ (4 days left until next service). I hope that makes sense and sorry if it is something that has been answered before - I tried to look but couldn’t find the answer! :slightly_smiling_face: Gareth

Hello guys! I’m new here and I’m handling a problem with one of my tables to link with Zapier I have a schedule tab when a person fill the form, a date filter to “exclude” that date from my available tab. It means, when a person schedule that date, in the {Schedule Date} is a linked record that I can’t get on zapier because it show only the ID record… I need to show the real date, cuz I wanna make a Zap to all the forms I’ll pass to a google sheet. Sorry if it’s a simples question, but it’s the last thing to end my project.

Hello, I have a master table with a column linked to subtasks from another table with statuses (launched, in progress, etc). In my master table, I’m trying to roll up all the unique values and if they’re not ALL ‘launched’, it should spit out ‘Incomplete’ otherwise spit out ‘complete’. Is this possible? Thanks!

Hello Airtable! I need some help with scheduling in a project management base I have. I’m trying to configure a column to alert me when I am scheduling a presenter for a conference panel that conflicts with another time slot where they are already scheduled. However, I am VERY new to airtable and the formulas needed to make this work. I tried using the formulas and suggestions from this post (Scheduling Database - Availability using SUBSTITUTE()) but there are some key pieces I’m missing that the posters gloss over (likely I’m not getting it cuz I’m new and am not as familiar with how linking and rollups and the formulas work - that’s actually me asking for clarification at the bottom). I have a table of presenters, and a table of panels. The presenters column in the panels table is linked to the presenters table. The panels column also has a column for start time and a column for end time. I just need another column “Conflict” to give me an alert when a presenter is scheduled for anot

Hello everyone, I’m a beginner with airtable. I imported a csv in airtable (i used to work with googlesheet), a database of people for a recruitment activity. I have several columns : name, email, etc… and 3 sectors in 3 different columns. I defined the content of my sectors columns as “multiple select” (because i would like after to make people fill through forms with multiple choices). I would like to have only one column centralizing all the 3 sectors of one person with different tag (which is called “options” in airtable, because the column nature is “multiple select”). If someone has “bank” in the first column, “insurance” in the second one and “energy” in the third one, I would like only one column with 3 “options” (like tags) : “bank” / “insurance” / “energy”. I have 3000 lines so I don’t want to do it manually. I already tried to use different formula but the result is not good , I have the 3 words in my target column , I don’t want the 3 words but I want 3 “options” in the sam

Hey guys, – What am I doing wrong on this? My goal is to: IF (OrderStatus contains “ :blue_square: Completed”, “ :brown_square: Closed”, or both-- it should be 1(true)

Greetings Airtable Community! I am hoping a more experienced user can provide guidance on a formula. I currently have a formula field: IF(AND({Guest Code}=‘DONOR’,{Status S1}=‘Yes’),First&" "&Last) This is returning the correct data (e.g. Robert Jones); however, I need to expand the formula to also include the guest of First Last (e.g. Robert Jones, Sally Smith). The guest field names are With First and With Last. Any assistance would be greatly appreciated. I’m up against a deadline, so it’s time sensitive. Cheers and thanks, John

I have an issue with the formula in the ID field not displaying correctly as it has several decimal places even though the column it’s getting the data from is set to currency integer (2). How do I get it to display correctly? This is how it’s showing: This is my formula for calculating the Amount Outstanding: And the format: And this is my formula for the ID field: Anyone know a way to fix this?

I’ve searched this topic but I’m obviously missing something because it seems too simple … I have a table of parts, each of the parts have a cost so the table has a Total column. I want to have that Total value appear in another table (then I’ll be deducting that value from corresponding columns to generate another figure. How do I simply reflect the value from a column in 1 table, in another table? Like I said, it seems crazy simple…

Hi there – There was a formula field option called “Last Updated By” - that seems to have disappeared? It was there at the beginning of January… is there another way to get this information now? Anyone know? Thanks!

Hola, si alguien me puede ayudar con una diferencia de fechas, donde: Corte: es la fecha final y Fecha: la fecha inicial, cuando Corte esta como tipo de campo Date y Fecha también, la formula abajo me funciona perfectamente, pero cuando Corte, esta vinculado a otra tabla cuyo campo es Date, la formula no funciona, probé anteponiendo a Corte, la fórmula DATETIME_PARSE({Corte}), pero no logro convertir dicho campo a data para que DATETIME_DIFF, lo entienda como fecha y resuelva la operación. Alguien me puede indicar cómo convierto ese campo vinculado a tipo data para que funcione la fórmula DATATIME_DIFF? (El campo que se diligencia es tipo Date y diligencia automáticamente el campo tipo vínculo, con una supuesta fecha, en la columna Corte) ROUND(IF({Fac Dep}>0, 0,IF(DATETIME_DIFF({Corte}, Fecha ,‘days’)+1>={Vida Útil}*365,{Vida Útil}360, (DATETIME_DIFF({Corte}, Fecha ,‘days’)+1)/365360)),0)

Is it possible to recreate the RATE() function from Excel and Google Sheets inside Airtable?

Hi, I would like to calculate how long a project will take based on the number of components * various pre-determined times. Here, for example, I’m saying that it takes 5 minutes to work on one Table. I would like to replace the 5 with 00:05:00, if possible. As this is an average time, I would like it to be part of the formula and not taken from another column. Thanks! (Datasets+{Time series}+{Supporting articles or methodologies}+2)*2 + ((Words*4)/500) + ((Charts+Interactives)*3) + Tables*5 + ((Datasets+{Time series})*1)

Hey. I’ve tried searching for solutions here, but haven’t found anything I was able to implement. What I’m trying to do is listing all my close friends and relatives, and creating a field that shows the number of days until their next birthday. Then I’d sort it by 1-9 so that the next birthday is on top. When the birthday is over, the “days until” should then jump back to 364, and therefor be sorted to the bottom. Could anyone assist me here? Thanks!

I have a field named Concatenated Attendee Name(s) with this formula: CONCATENATE(First&" “&Last&” “&{With First}&” "&{With Last}) I have another Multiple Select field named Guest Code. One of the many choices is ‘Scholar’. How would I edit the formula above to only concatenate names that have the Guest Code of ‘Scholar’? Thanks very much!

HI fellow Airtablers, I am building a priority tool for myself that, at it’s core, has a list of tasks. Each task has a total priority score. Now tasks can have dependencies and I want to add their total score to the “total priority score” of the top(or root) task" This works for one level deep but not recursive. Airtable gives me an error regarding a circular reference. Which is logical if I would link a task to itself but I don’t… Anyone have an idea how I can fix this? Thank you and I will post the solution if I find it. I can’t add images or GDrive links so please DM or something for screenshots of my table

I am trying to write what I believe would be best as an IF THEN statement but I’m open to suggestions. I have a column named client payment date and there’s a number, 1-31 there. I want it to print out in a different column (Which I’ve named “Payment Transfer Date”) one of three things. If the number under “Client Payment Date” column is between 1-10 then I’d like the “Payment Transfer Date” column to display 15th. If the number under “Client Payment Date” column is between 11-20 then I’d like the “Payment Transfer Date” column to display 25th. If the number under “Client Payment Date” column is between 21-31 then I’d like the “Payment Transfer Date” column to display 5th. Any help would be greatly appreciated as I feel I’ve gone above the skill level on what I know.

Hi everybody, in Excel you can use a MAX formula and set it in such a way that it renders the top value, the 2nd best, 3rd best and so on. Is this possible in Airtable? The Max formula doesn’t seem to allow me to add these parameters. Regards, André

I have a spreadsheet that I am using to keep track of when a document is returned. The primary field is the person’s name, then there is a column to record a specific program that person is in (set as a single select), then the next column has a check box. The check box will be selected when the person returns their document. I’d like to record how many people return their documents by Program. So how many people in Program A have returned their documents, Program B, and so forth. Is there a formula to create this? Ideally, I’d have a second table that has the Program name in the first column and then the total in the second column. Essentially, a countif formula.