Formulas

I’m Looking for Excel Vlookup option, Where I need to fetch data using the other table based on the value keyed in. I can do it now using lookup and linking the sheets but the problem is every time, there is a pop up opened which is inconvenient.

Hey, Airtable Community! I’m building a Running gamification system in airtable and right now I’m Stuck in counting the Number of records (Activities) per week Nº. In my base, I have a table with the participants - which is a Link to another record: another table with a database of people - and one with the recorded activities - which has lots of fields, The important ones for this solution being the “Activity Day” - Which I use a formula field to extract the week number. How Can I create a formula/rollup that counts the number of activities in a week?

Hi I have been looking for ages but just can’t work out what I need to put. I have a url field and want to create a formula to be able to group records from where they came from say ‘airtable.com’ or ‘youtube’ as a ‘live’ way to group. So easy with filters - wish you could use ‘contain’ in formulas i would be laughing! Thanks, Maria

I’m constantly making tables to organize information into hierarchies. I also want them all to have the same type of information, and to be groupable/etc, so they have to be on the same table. I’ve tried working with cross-tables, but even then I run into formula circular error prevention. All I want is a sub-sub-item to inherit the ID numbers of both parents in its name, in order, and I can’t for the life of me figure out how to do it. So if My hierarchy is like this: Item 1 Sub-Item 1.1. Sub-Item 1.2. * Sub-Sub-Item 1.2.1. Sub-Item 1.3 Item 2 Item 1.2.1 to inherit that number in front of the name, so I can see it on linked records. Thoughts? (I’m sure it’s been done before, but. No dice on the search yet.) I know that blocks can handle hierarchies now, but I’m too broke to pay for a subscription (yet!!). And I’m not 100% satisfied on how it works anyway, from what I can see. And I guess there’s zapier, but anything useful requires too much edit volume or too complicated to again

Hello All, I am looking for a formula that will calculate historical orders placed with the option to query 14, 30, 60, 90 and all time order history. Is there a way to do this? I am very new, go easy on me :slightly_smiling_face:

I’m trying to write a formula which returns either a value from field A or a string within the formula, dependant on the value in fields B & C. Where the values in fields B & C are met then a value within the formula should be returned, when the value in field B is not met then a value from field A should be returned. My table has 3 fields: Client Name Currency Customer Code Where ‘Client Name’ is ABC and and ‘Currency’ is USD then return a string from the formula. Where ‘Client Name’ is ABC and and ‘Currency’ is GBP then return a different string from the formula. Where ‘Client Name’ is not ABC then return the ‘Customer Code’. I’ve tried using nested formulas for the first 2 conditions but I do not know how to include the last condition. Can someone please help with this?

I need to create a formula that does this: Attendee Count < 100 put in 1 Attendee Count > 201 < 1000 put in 2 Attendee Count > 1001 < 5000 put in 3 Attendee Count > 5001 put in 4

I want to know what is the proper formula to use this… IF(Checkincount= 99, TRUE(), FALSE()) I’d like to have an or option meaning IF(Checkincount= 99, TRUE(), FALSE()), OR IF(Checkincount= 199, TRUE(), FALSE()), OR IF(Checkincount= 299, TRUE(), FALSE()) I can’t seem to find the right formula to use the OR Meaning, I want it to show if Checkincount is 99 or 199 or 299 etc… Hope that makes sense. Thanks

I’ve got an opportunity tracker base that’s logging potential project opportunities from five different countries. I’m trying to set up a column that takes those individual budgets and converts them all to USD so that I can see our global pipeline in one currency. To do this, I’m assuming I need to set up a formula that cross references each project budget against the city of origin for that project and only does currency conversion if necessary. Basically I want to do something along the lines of If {City of Origin} = “New York” or “Los Angeles”, then just reprint {Budget} If {City of Origin} = “London”, then just multiply {Budget} * 1.3 If {City of Origin} = “Paris”, then just multiply {Budget} * 1.4 etc etc But I can’t figure out the syntax. Would anyone be willing to help? Also, can anyone recommend best practice for referring to currency conversion multipliers in Airtable that might be simpler than hard-coding the numbers right into the formula? Thanks so much in advance for any h

Hi. I have a database with veg and flowers in. For the flowers I have fields for colours, scented and evergreen. I don’t want these to appear in the veg details. Same as I don’t want ‘hungry gap’ fields with flowers (for example)… Is this possible or should I split the data? If is is, please could you give me a forumula example I could use.

Hello All, I’m implementing a base that will be used to manage a process. As any process it has multiple steps and I want to identify which of them are bottlenecks. In order to do that, I created a Single Select field (Process_Step) that represents the current step of the process and other n fields that will hold the time that the process reached each step. For instance, S1 will hold the date and time when the process moved to step 1, S2 to step 2, and so on. I implemented it as follows: Field Last Updated Time = LAST_MODIFIED_TIME({Funnel}) Field S1 = IF({Process_Step} = "Step_1", IF({Origem} = "Maping", {Last Updated Time}, BLANK())) The field Last Updated Time contains information about when the process moved one step and S1 will hold that value if it has entered the first step. The problem is, every time the process moves to further steps the formula is recalculated and S1 becomes BLANK(). Any suggestions about how to make it work? I was thinking about using the Airtable Python Wr

I have been using this formula: IF({Completed}=1,DATETIME_FORMAT(NOW(),‘M/DD/YYYY h:mm’),0) to record a date when a “Completed” check box is ticked off in another column. This worked flawlessly up until recently. For some reason, all past dates are now changing to whatever the current days date is. I have jobs months old that now say they were only completed today. Any help is appreciated. I can’t figure out what happened other than it is a new year and maybe that messed something up.

I am a formula newbie and need some help. I am hoping this is even possible. Any help is welcome!! If the Expiration Date field is TODAY then I need to have Executed Agreement On File? field changed to No and the Status field changed to Expired. These values are from picklists. Can this be done and if so, how do I write it out? Thanks much!

Hi everyone, I am trying to auto publish content to my site according to formula in my table. My curl filter is - filterByFormula={diff}=1. The filter is working great but it seems my logic is lacking when it comes to determining what formula to use to get {diff} =1. I have a table that includes a column {publish-date}. {publish-date} is set to US 24hr format. In my {diff} column I tried IS_BEFORE({publish-date},TODAY(HOUR(8))) and IS_AFTER({publish-date},TODAY(HOUR(8))). It worked for one day then went screwy. In the end I want content from my table row to appear on my site once the current time is past it’s {publish-date}, specifically after 8 AM of it’s {publish-date} .

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