Formulas

I’m trying to make a formular to that’ll use certain information if it’s after a particular time stamp. However no matter what I do I seem not not be able to figure out how get airtable to tell me if a certain date is after another. Sofar I’ve been trying variations of the following. Possible someone has an idea to what I need to do? IS_AFTER(DATETIME_PARSE(‘22/1/2020’, ‘DD/MM/YYYY’),{Date stamp})

I’m trying to return the same number if the value of a cell is within a specific range. If the cell C value is within .875-1.125 it returns $29. If outside of that range it returns $56. This is the formula I tried that didn’t work: IF( C>=.875, 29, IF( C<=1.125, 29, 56 ) )

Hello everyone, Context : I am trying to calculate how many days one task, and a whole project (which is a compilation of many tasks) takes to be done. The difficulty here is that I need to exclude in the days the hours that are not worked. We assume that we are working between 9:00 and 19:00, which makes 10 worked hours a day. We also assume that we are not working on Sundays. Knowing this, I have 3 different columns : The first tells the date and hour of the beginning of a task The second tells how much total time the task takes to be completed The third one is supposed to give me the end date and time of the task when it is completed, knowing all of our assumptions. So I am trying to build this formula where I could calculate the 3rd column. Does anybody have an idea ? I tried to look in the forum and I was surprised that this question had never been raised Thank you very much

Hi, I have a column that has a drop down box that has different options: Very Low, Low, Medium, High, Very High. I have set up a substitute formula in a new column that replaces the above phrases with a number: Very Low = 1, Low = 2, Medium = 3, High = 4, Very High = 5 My substitute formula looks like this: SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Level of Influence}, “Very Low”,“1”), “Low”,“2”), “Medium”,“3”), “High”, “4”), “Very High”,“5”) The results that are returned work unless you select “Very High”. It shows up as “Very 4” rather than “5” in the column with the new number value. I can’t figure out why the first four work fine and the last doesn’t. It looks like the substitute formula is replacing the “High” in “Very High” with the “4” Can someone help me figure out how to fix this?

I’d like to have a column which will display if it’s time for a page to be updated. It should look at my “Last Updated” column date and if today’s date is less than 1 month from then it from then it would say “Current”, if more than 1 month it would say “Needs Update”, if more than 2 months it would say “Update Now”. Any thoughts? Thanks!

Hi I am designing an inventory management system I have a column in our master inventory table which says ‘sold?’. it is a checkbox column. I want the record to be moved to my other table called ‘sold items’ when i check the box so I can keep a track of the total asset value. How can I do this? Please consider that I am new to this. Many thanks

If I’m remembering my FileMaker days correctly, I had the option to have a field filled by a formula/action, and I could then enter something into that field if I wanted to change the info. For example, I’m working on an attendance database in which I check students by creating a record. The creation time of that record is used by a formula field to determine if they were on time or late. I’m trying to figure out what to do if, for example, I step out for some reason and end up logging them in later than they arrive. In that case, I’d want to override the “calculated late”. The best I’ve come up with is to have 2 fields: 1. a Manual Entry Late/Present field and 2. the Formula Based Late/Present field that includes an addition to use the value from the Manual field if it’s not empty. Is there a better way?

Hi, I have a table with rent items. There is a single select field “availability” with “available” and “rent”. Right now i set the “availability” manually if a new linked record “customer” has rent an item. Now I want to automate the things :slightly_smiling_face: As soon as I choose a customer from a list, the single select field “availability” should switch automatically to “rent”. This doesn´t work… IF(Customer = BLANK(), {Availability}=“available”, {Availability}=“rent”) Can someone please help me out? Thanks

I know this has been discussed before and I have read the questions and comments. Unfortunately I am stuck. I have a multi select field that I am trying to extract information from. The formula works fine in Excel =IF(O2=“Individual”,15,IF(O2=“Family”,20,IF(O2=“Social”,10,IF(O2=“No Cost”,0,“0”)))) I have tried the following and none work: =IF({Membership Test 2}=“Individual”,15,IF{Membership Test 2}=“Family”,20,IF{Membership Test 2}=“Social”,10,IF{Membership Test 2}="No Cost”,0,”0”)))) =IF({Membership Test 2}={Individual},15,IF{Membership Test 2}={Family”,20,IF{Membership Test 2}={Social},10,IF{Membership Test 2}={No Cost},0,”0”)))) =IF({Membership Test 2}=Individual,’15’,IF{Membership Test 2}=Family,’20’,IF{Membership Test 2}=Social,’10’,IF{Membership Test 2}=No Cost,’0’,0)))) Can anyone help? Thanks

I run a cat sanctuary and I’m wanting to keep track of vet charges for each individual cat. So I would make a Table for Harry and then I would create a record with all the charges for a certain date. But can I actually do that? The way I’m looking at it now, I would have to create a separate record for each individual charge. Is there a way I can create one record for each invoice?

Has anyone else found that the SEARCH and FIND formulas count unicode characters (like emojis) as two positions within a string, as opposed to one position. This means that both functions seem to return the incorrect index if the matching string starts after any unicode chars in the text to search. Short of stripping out any unicode characters, does anyone have ideas for other workarounds?

I have volunteer applications imported from 123formbuilder to airtable using Zapier. Some parts of the application have radial selections, where an application can choose a predetermined option, or they can choose “something else” and enter a custom value in a short text field that becomes available. When this data is sent to my base, there becomes two columns, one that populates with a primary selection and a second one that will either be blank or has the applicant’s custom response in it. I’m trying to organize this data into one column that either pulls from the first column if the second column is blank, or chooses the second column if it is not blank. What is the best way to accomplish this? I’m fairly certain I need an IF formula of some sort, but I don’t know enough to know if there is a better way. An example of this can be viewed here: https://airtable.com/shrje2Jcz0ww2BrP1 I c

Maybe its the new year but I am having a trouble trying to work this out. :slightly_smiling_face: I need a cell to work out 7 postal days from a specified date and add it to a number based on a date column. for example. date column has the date of today entered. 16th jan. 7 working days from today makes that the 27th Jan then it needs to add on another 14 calendar days and display the date it works out. Im scratching my head here! lol thanks all!

I have two tables. Table 1: Client Table 2: Appointments Each appointment is tied to a client. I’m looking to see how many days from the client’s last appointment. E.g. the client’s has 3 appointments however his last appointment was 1/1/2020 then the cell would display ‘14’ since that was 14 days ago. Any ideas?

I have a Project table with a Completed field which looks at a Final Date field and via a formula checks if today is past the Final Date field it marks the Completed field as YES for the Project record. I will be adding a trigger via Zapier to email someone that the Project record is complete. I need to add to the Completed field formula the ability to know if today is a weekend and if so to wait to mark the field as YES and trigger the email on the next weekday, Monday. Thank you in advance.

I have created a database in Airtable to keep a log of my Airbnb guests, income, etc. I’m using the Zapier Email Parser to grab data from email I receive whenever we get a new booking. In the email we’re provided with a variety of data including the date of checkin and checkout, in the following format: Nov 14, 2019 Nov 17, 2019 The challenge is that these dates cannot be imported into an Airtable DATE field… they can only come in to a TEXT field. What I want to do is have this raw date “Nov 14, 2019” converted into an Airtable DATE field instead of a TEXT or FORMULA field type. I can easily have this data added to my checkin and checkout fields, and use the following formula with date specifiers: DATETIME_FORMAT((date),‘YYYY-MM-DD’) which will turn “Nov 14, 2019” into “2019-11-14” Is there a way to then take that date and have it populate an Airtable DATE field? I don’t mind hav

Using the Airtable API, so I want to make this query concise as possible. I want to filter using formula to remove entries that contain specific values from one of it’s fields. For example: I want all entries where Field A is not equal to (1,2,3, or 4). Not sure how to structure the formula to accomplish this.

Hello! On my website we offer consultations and have just switched to booking those through airtable to get exactly the information we need in the initial form imported directly to a base. What I would like to do is have a date field that is set up to calculate what 1 hour prior to the meeting is so I can have Zapier send an email reminder to my clients. Is this possible?

Hi, I am trying to use the FROMNOW() function to tell me when a transaction has expired within the last month. Can someone explain why this formula does not return accurate results? IF(FROMNOW({Expiry date}) <= 1, ‘month’, “Recently Expired”) It works (i.e. it doesn’t return an error) but it is returning “Recently Expired” for every transaction that has expired, even ones that expired years ago! Help please! :slightly_smiling_face: Mark

I am trying to create a nested if formula. I am trying to build the following formula: If a # value is over 1,000,000, the field would fill with “Mega Influencer” If a # value is 100,000-999,999, the field would fill with “Macro Influencer” If a # value is 10,000-99,999, the field would fill with “Micro Influencer” If a # value is 1,000-9,999, the field would fill with “Nano Influencer” If a # value is BLANK, the field would fill with “Need to add follower count” I have come up with the following formula and it is not working:

Hi All, Brand new here. (I tried attaching a screenshot but can’t so… ) I have a Related Field column where I’ve added some ‘Components’. Those Components have a Price field within their table. Next to the Components field I have a Components Total field. What’s the formula required to display the total of the Components in the total field as they are added? Boy I hope that makes sense. Thankyou.

Hi, is there a possibility to create a hyperlink in the text that is written in a cell of the “notes” column?

I have two date fields and would like to pull out the months and years in separate fields from one or the other. I managed to get the single IF working but not the nested function. Here’s my formula: IF({Date A}, DATETIME_FORMAT({Date A},’YYYY’), IF({Date B}, DATETIME_FORMAT({Date B},’YYYY’))) which I based on: IF([logical argument1], [value1], IF([logical argument2], [value2], [value3])) Any help would be appreciated.

EDIT: It’s not letting me upload pictures, anyone know why? I have 2 tables. 1 called Members and 1 called Appointments. Here’s the members table: Each member has a Name, Membership Type, Shared with (linked to existing table), and another column linked to ‘appointments’ table. Here’s the Appointments table: You’ll notice a the field “Total Appointments per Active Member” Anyone that has membership type ‘shared’ means that I don’t count them as an actual member. I want to link them to the original member. And since I want to track each appointment by the main member, I’d like to figure out how to add the members ‘shared’ total with the existing membership. E.g. John Smith has a total of 3 appointments (his 2 booked, and 1 fro

I’d like a formula field to return =today() if another field is blank, but if the other field is already populated, to use that date instead. Any help crafting this?