Formulas

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.

Hello! I’m trying to write a formula to get a bell emoji if a record has been modified after the date we close our quarterly reporting by. I have the “last modified” field and the “report closure” dates to compare, but I am struggling to write the “IF” formula in the right way. Any help would be massively appreciated! :grinning_face_with_big_eyes: Thank you! Belén

Hi all, I’m trying to build a formula field that only displays the dates of records that have “Event”. My formula is: IF(FIND(“Event”,Name)>=1,DATETIME_PARSE(Date),"") My FIND() condition works, but outputs the ISO date format of {date}. My goal is to output the format exactly as it is in {date}, a date object. Anyone know what I’m missing from my formula? E.g. 2020-02-25T00:00:00.000Z should be 25/2/2020. Thanks all.

Hi there, I’m trying to work with video timecode and the DURATION field type doesn’t cut the mustard, as I need HH:MM:SS:FF (frames) formatting. See another post on this at Video Timecode Field I’ve found a great pair of Excel formulas to convert FRAMES into HH:MM:SS:FF and back, but I would really appreciated help turning this from EXCEL into AIRTABLE syntax please, where cell A1 could be another text field… =VALUE(LEFT(A1,2))6060*30+VALUE(MID(A1,4,2))6030+VALUE(MID(A1,7,2))*30+RIGHT(A1,2) Example: A1: 01:06:46;11 RESULT: 120191 =TEXT(MOD(INT(INT(INT(ABS(A2)/30)/60)/60),60),“00”)&":"&TEXT(MOD(INT(INT(ABS(A2)/30)/60),60),“00”)&":"&TEXT(MOD(INT(ABS(A2)/30),60),“00”)&";"&TEXT(MOD(ABS(A2),30),“00”) Example: A2: 112019 RESULT: 01:02:13;29 Thanks in advance!! (The formula above came a very helpful post at https://github.com/cgranier/Excel-Timecode )

Could someone tell me what I’m doing wrong? For some reason this formula is able to input the money amount for job 1, 2, 3, and 5 but it shows up blank for job 4. What’s going on? IF( {Job Type} = ‘Job 1’, “$ Amount”, IF( {Job Type} = ‘Job 2’, “$$ Amount”, IF( {Job Type} = ‘Job 3’, “$$$ Amount”, IF( {Job Type} = ‘Job 4’, “$$$$ Amount”, IF( {Job Type} = ‘Job 5’, “$$$$$ Amount” )))))

Hi guys, The result of my formula is not recognised as a date… Here’s what the formula’s supposed to do: Firts I set the project deadline date. To execute the project a few actions needs to be done. I made a single selection field to choose how much days or weeks in advance the action needs to be done. For example “1 week” in advance. The formula I created is fairly simple: [Deadline date] - [Select time] = New date This is the code I made: IF({Select time} = “1 week”, DATETIME_FORMAT(DATEADD({Deadline date}, -7, ‘days’), “DD/MM/YYYY”) Can anybody help me so that my result is recognised as a date? I tried leaving out the " " but it gave me an error. Thanks!

Example: I have 3 fields, 1 multi select, and 2 checkboxes Reason - Summary: Multi-Select Reason - Wedding: Checkbox Reason - Vacation: Checkbox What I want to do is be able to update the value of the multi-select field based upon the checkboxes that correspond to an option. For instance: IF( {Reason-Wedding} = 'True"), THEN(select “Wedding” from {Reason} options) If this is easier done upon importing the record using Zapier could you please point me in the right direction.

Hello, I’m also new to Airtable. I’m wrestling with one thing that is very easy in Excel (or Google Sheets), but I haven’t be able to find how it can be implemented in Airtable. I’ve created a Table. I add a record ever day to track the number of occurrences such as: Visitors to an establishment. Date Day Visits % Change 23.3. Mo 27 NA 25.3. Tu 28 = (28/27)-1 (in formula in Excel (C2/C1) - 1 26.3. We 30 = (30/28)-1 (in formula in Excel (C3/C2) - 1 27.3 Th 35 = (35/30)-1 (in formula in Excel (C4/C3) - 1 etc Where the % Change Column (or Field in Airtable speak) is, of course, calculated by a Formula. How can I achieve this with Airtable? Any help would be very much appreciated. Peter

I want to formulate a date if: under one column the answer is 3-day add 3 days to date under one column the answer is 2- day add 2 days to date under one column the answer is 1- day add 1 day to date I think I begin like this, DATEADD({Proposed Date of W/C},3, ‘days’) but how do I formulate the if this then that?

Hello community! I am in need of your help. I am concatenating text in various cells. When all cells in (Fields 1-6 is filled the result is perfect. However when data is missing from some of the Fields it generates a double underscore. How could I avoid this nuisance ? Formula being used: CONCATENATE({Field 1}, “", {Field 2}, "”, {Field 3}, “", {Field 4}, "”,{Field 5}, “_”, {Field 6}, Extension) Thanks you so much and stay safe!