Formulas

Hi Everyone, i’m loving Airtable but am still quite new to the formulas. i am inputting dates of my staffs first aid and criminal record checks to expire after 3 and 5 years. i’m hoping to have the date issued in on column and the next have active or expired… but dear god i cant figure it out! anyone give me a hand here? thanks in advance.

I’ve got a datetime field called Start time (UTC) I want to take the time component and reformat the minutes into a fraction. For example: Source Desired output 14:00 14 14:15 14.25 14:30 14.5 14:55 14.92 I have tried taking the minute component and conditionally dividing it by 60 to at least get started (before I sort out rounding etc), but I just get #ERROR. Here’s my formula: IF(DATETIME_FORMAT({Start time (UTC)},'m')=0,"",(DATETIME_FORMAT({Start time (UTC)},'m')/60))

I want to use a formula to add the Collaborations initials to the Event name so I know who is going just by looking at the primary field name in the calendar view but I am unclear how to accomplish this.

I need a field to add 10% to the previous totals column and then another field that adds 20% to the previous, my experience in formulas is basic +,-,x & divide, and I cannot find the simple solution yet, please help

is it possible to remove spacing between words using formula

Hi, I would like my first column to be a formula that combines columns A-B-C if a specific value is selected in a single select column and combines different columns if another value is selected in that same single select column. I have a single select column called {Calendar} with 2 value options “Event” or “Series”. If {Calendar} is selected to be “Event” I’d like the formula to combine {Calendar}&"-"&{Date}&"-"&{Client}&"-"&{Street} If {Calendar} is “Series” I’d like the formula to combine {Calendar}&"-"&{Date}&"-"&{Site}&"-"&{Service} I would appreciate any help - I am completely stuck and need this in order to merge my Event and Series bases. Thank you! Alix

Is there any way to use the number of records in a tab in a formula? Eg: tab 1 > column 2 has a number x and in column 3 I want a formula that divides that number x by the number of records in that row Question form a colleague to calculate some Business Continuity Management score…

Hello Everyone, I am looking for a way to manage my blogs on airtable ( texts, pics, etc…) and display it directly on my WordPress website from there. Is there a way to do so? Thanks a lot!

Hello, forgive my English, I’m Brazilian and I’m having difficulties with the use of the IF command. First, I have 5 different dates, which I will use as a review in the studies, and I needed to compare all these dates at once with the current day, so if date 1 is less than today, date 1 would return to me, if it were bigger i would return date 2, however, if date 2 was smaller, i would return date 3, and so on. Does anyone have any idea how to do this? !

My formula is MONTH({Trial Date})-1 I want that to then return a month name instead of a number. I’ve been trying to figure out the DATETIME_FORMAT to do it, but I cant get it.

Newbie here so be kind :slightly_smiling_face: This might have been asked before, but the only one i found that was a little relevant, the poster was asked a few times why he/she isn’t using the same table for their needs. So lets say i am insisting on using a few tables. It is for inventory. I will create tables for example: Available (featuring a column for manually entered/edited qty) Incoming (featuring a column for manually entered/edited qty) Outgoing (featuring a column for manually entered/edited qty) I will have a main table for an ‘overview’ of the combined above qtys. There wil be a ‘Total’ column in this main table. In this main table, the qtys from tables: Available Stock + Incoming - Outgoing will be calculated if i can work out how to make the tables work together? For example, in Google sheets or excel, a formula between sheets would be like: =‘ORDER LIST’!C116+‘SUPPLIER 1’!C9 So how to make a similar formula for Airtable? (Tried adding images for your reference but i

Hello I’m new in the Airtable community, and formula are definitely not my area of expertise, I’m trying to make a formula that counts the days of difference between requested return date and the real return dates, however if dates are the same i want the cell to stay empty. like : Date return : 02/02/2020 real return : 02/13/2020 Delay : 11 i try with this formula without succes : IF(IS_AFTER({Real return dates}, {Date return}), DATETIME_DIFF( {Real return dates}}, {Date return}, ‘days’ )) Sorry if this sounds dumb or simple, i’m very new to airtable and formula! Thanks alex

I’ll preface this with a “Key” that gives context to my field types and field names. [Date #1] = “Start Time” Date Field (set to GMT) [Date #2] = “End Time” Date Field (set to GMT) [Stream URL] = URL Field I’m trying to make an IF() statement in a formula field that would reveal the [Stream URL] if the current point in time (down to minutes or seconds, I don’t care which) is after [Date #1] and before [Date #2]…otherwise, reveal the text “No Stream Available” I have tried things like this and come up short" IF( AND( IS_AFTER( NOW(), {Start Time} ), IS_BEFORE( NOW(), {End Time} ) ), {Stream URL}, "No Stream Available" ) Help would be appreciated :pray:

I have 3 tables: 1.Assets 2. Contacts & 3. Maintenance calls. If I have a form to start a call for a repair and I ask for a phone number, how can I add automatically to the form (or later, to the table itself) the info about his contact details and the asset, based on that (the assets and contacts are linked together) Thanks a lot

Hey there, I have a table where all the leads are automatically copied to fields. I’d like to count how many leads/calls/sales I have made based on the date. Can anyone help me with that?

Hi i am in New Zealand and our date format is dd,mm,yyyy and I am trying to calculate years and months using this code: DATETIME_DIFF(TODAY(), {Date Of Birth}, ‘years’) & ’ years, ’ & MOD(DATETIME_DIFF(TODAY(), {Date Of Birth}, ‘months’), 12) & ’ months’. It is working however only on this date format (mm,dd,yyyy) how can i change this so it works for me please?

My base is for keeping track of genealogy research projects. The Main Table is a list of projects that links to a People Table and a Timeline Table People Table: Has columns for First, Last and Middle name. Has a column that concatenates to list the Full Name and the Primary record field Timeline Table Each record is linked to a project from the Main table and a person. The same person can be linked to multiple timeline records Has a lookup column to display the Full Name field from the People Table Main Table Lookup pulls the Full Name from the Timeline Table and repeats it since a person can be linked to multiple records in the Time Line I want to create a new column in the Main Table that lists the Full Name only once. Something like this: List the first name in the list, if the second one is identical, then don’t list it, etc. through the list of names in the lookup column. I’ve really had good luck finding formulas for what I want to do by searching the forum, but not having lu

Hi guys I’m trying to use the formula IF to return some values. This is my example: I have 4 conditions, wich are the number of words that a text must have: 500 words 1000 words 2000 words 2500 words And to each one I have a specific value To 500 return 30 To 1000 return 45 To 2000 return 55 T0 2500 return 70 I tried to use the formula that I use in Google Sheets: =IF({number of words}=500;30;IF({number of words}=1000;45;IF({number of words}=2000;55))) I tried to use this way but it returns error. How could I use this formula in my case?

Hi there. I am a newbie to Airtable but loving it so far. However, I am unfamiliar with using formulas in Airtable. I am testing it out for use as a fundraising database / CRM. I would like to have a column (field) in the Donations table which sums up all donations to-date for the current year by Donor. I have a Donor table with unique Donor ID identifier which is linked into the Donations table. So, for example, John Doe donated $100 in January 2020, and then he donated $50 in February. I want a Total Donations To-Date 2020 field, which in January would automatically populate with $100 and in February after entering the new donation would update to $150. How can I do that? TIA for all responses.

Hi everybody, I’m struggling to find the right code for my formula. I’m making a content calender in airtable. I made a text field “text input” and I made an attachement field “image”. My goal is to have a field that tells me the status of the content. What I’m looking for is a formula that kinda goes like this: IF {text input} is empty then: “ :bell: write text” IF {image} is empty then: “ :bell: upload image” IF {text input} and {image} are empty then: " :bell: :bell: write text, upload image" IF {text input} and {image} are not empty then: " :white_check_mark: Ready to go" Can anybody help me how to get the right formula? Also… (to make things interesting) I was wondering if there’s a way to add a “deadline” feature to the same formula. My question is: is it be possible to add something like this too: IF {text input} or {image} is empty, and {deadline} is within 2 days, then “ :rotating_light: HURRY” Hopefully someone can help me out with this. Thank you guys so much for yo

Hi, I’m using a lookup field which aggregates a list of single selects (containing values like : “Yes”, “No”). Within the lookup fields, I’m using the formula : ARRAYJOIN(values) It works great. Then I have a formula field with a FIND() : FIND("Yes", {my lookup colomn}) The result I get with this formula is way below the amount I am supposed to get. About 3 times lower. Even stranger, if I change the lookup formula to ARRAYJOIN(values, ', '), I get yet a different number. What gives? Thanks for your help!

Hello Airtablers! For my CRM I’m trying to automate the ranking of my prospects from “ :fire: Flaming hot” to " :skull: Dead". I tried using a SWITCH formula but got in difficulties, so I reverted to IF and DATETIME_DIFF. I want to switch between the 6 values below and though I managed an IF statement with just 2 values, when I try 6 (and DATETIME_DIFF) I get no-where (I even checked for curly brackets, straight quote marks and the number or parentheses)! Can you help? I tried the following: IF({Last Contact}=TODAY(),"🔥 Hot!", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>3,"🌡 Warm", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>7,"😎 Chilled", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>21,"🚰 Tepid", IF((DATETIME_DIFF{Last Contact},TODAY(),'days')<90,"🥶 Cold", "💀 Dead!") ) ) ) ) Thanks in advance!

We use Airtable to manage a sourcing catalog. We gather price quotes from vendors in one table and use those quotes to build out customer orders in another. Selecting a quote record applies the price to customer orders. This means a single product can have multiple quotes associated with it for different quantities ordered. Quotes have a date, quantity, and price associated with them. I currently use a Lookup field to display the associate quotes per product into a view that we share with customers so they can see product descriptions, photos, and the prices at different order quantities. Table A (Product Info) Looks Up linked quotes in Table B (Vendor Quotes) The problem is that vendors might change prices over time and I don’t want the Lookup field to display quotes that are not the most recent, but I still want the Lookup field to display a quote for each quantity. Currently, if in March I receive quotes for a Product A at 500 units, 100 units, and 50 units my Look Up field will dis

I am trying to transfer my google sheets formula to airtable and I’m not sure how to do it. I have a form where people enter data. I want to create a possibility for me to look through that data from a certain date range and give me the sum. Here is the google formula, please let me know if you can help me transfer that to Airtable. Time log is a “sheet” or tab or page (whatever you want to call it) in my document =SUMIFS(‘time log’!E:E,‘time log’!B:B,C4,‘time log’!C:C,">="&A4,‘time log’!C:C,"<="&B4)

Hi… I understand spreadsheets but am new to databases. I have Base A that contains data about PhD students. The first field is Surname/FirstName. The base holds around 160 fields including 3 separate fields for 1) Principal Supervisor; 2) Associate Supervisor #1; 3) Associate Supervisor #2. I have a second Base that contains data about the Supervisors. I would like to create a field in this 2nd base that pulls the Surname/FirstName of every student from Base A where the supervisors name is present in Principal Supervisor; Associate Supervisor #1 or Associate Supervisor #2. Is this possible? In excel I think I would do this with an IF - VLOOKUP formula but I can’t see how to reference the other table.