Formulas

I am looking to take a long string of comma separated numbers (it is a string from a look up). Here is an example of numbers to summarize: 1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.33-15, 1.33-16, 1.33-17 If you look closely, there are missing numbers. I am hoping to find a formula that will output: 1.17-01 --> 1.17-14, 1.17-17, 1.17-18, 1.33-01, --> 1.33-05, 1.33-07, --> 1.33-17 I am flexible on the “–>”, but as you can see a simple dash won’t work, because dashes exist in the numbers. Has anyone seen something like this before? Any help would be greatly appreciated!!

Hi 3 tabs: vacation: form > start and end date vacation + linked records to “employees” so they can select themselves employees projects: per record 1 day to work on a project + linked records to “employees” to select technician Question: is there any way to signal in any way (preferably not being able to select the technician in question) that the selected/to be selected technician is on vacation on the workday in question?

I’m trying to create a single-select field for easier sorting based on a compiled age based on the Birthdate (DOB) column. Here are my formulas: Age column: DATETIME_DIFF(TODAY(),{DOB}, “years”) Which returns the age of each student. Age Groups Column: IF( {Age} <= 2, ‘PRE-K’, IF( {Age} = 3, ‘3-5’, IF( {Age} = 4, ‘3-5’, IF( {Age} = 5, ‘3-5’ ) ) ) ) Please tell me there is an easier way to do this lol. I want it to autocompile, but I’d like it to be single-select rather than return plain text for the visual aspect with color coding. I hope this makes sense! The “Age Group” column is how I would like it to autocompile, allowing me to delete “Age Groups.” Attaching a photo for reference!

Hi, I am having some issues with some fields that I’m rolling up. My goal is to use an automation via Integromat to take a Rollup field from a multi-select field in Base 1 and populate that data in Base 2 as another multi-select field. The rollup itself is generated from data in Base 1, where I have a “Posts” Table that shows various Social Media Posts. Each Post can be in multiple categories in a Multiple Select field, and each Post will link to a Report on a “Reports” Table. On the Reports Table, I am using a Rollup field to get all of the unique Categories associated with all of the linked Posts. This works perfectly when each Post only has 1 Category attached to it, but when the Post has multiple Categories attached to it, the data doesn’t show up or send to Base 2 correctly (like shown in the first screenshot). I have tried plenty of different things but can’t seem to get the data to populate correctly. I believe the issue has to do with Airtable reading each multi-select field

In my base, I have 2-tables. The first table contains all of my primary data—the second table with additional data related to my first table. In table-1, I have a column named “month number”, which means a number will represent a month; i.e. 2 = February or 12 = December etc. It looks like this: Month Number 1 5 8 9 In table 2, I have a column named “Month”, with all the months of the year in their own lines. Example: JAN FEB MAR Next to that column, I have a column named “Month Number.” Which obviously resulted in having numbers 1 to 12, next to the months of the year. In my first table, next to the column named “Month Number” I’ve added a column called “Month_Name”. In this column, I want the column to identify that a number represents a specific month name and fill in the column with the corresponding name. For example: If 4 = APR, then I want APR to appear in the “Month_Name” column next to it’s “Month Number” column. So it will look like this: Month Number 4 Month_Name April Your

I need to obtain a simple margin profit. the formula i use in excel is this one: =(retail price / wholesale price) - 1 Everytime I try it doesn’t give me the right answer.

Hello! I am using airtable to track tasks that have been completed before a new hire starts. I currently have a Start Date (Field type is Date) and a Laptop Ordered (Field type is Checkbox). I would like to create an IF formula that comes down to: if laptop order is not checked AND the start date is less than 7 days, i get a message that says “late: needs ordering” I cannot figure out how to do IF functions with dates and then how to incorporate it into a larger, nested formula. Currently all I can get is the Check box to work I have: IF({Laptop Ordered}=1,“On Track”,“Late: Needs Ordering”)

Hi, I am trying to create a formula that will associate tags with a given row if that row contains text that matches entries in a row from another table. For example, we have a table that contains a set of emails received: Lisa spoke to a at b company Kyle spoke to c at d company Judy spoke to e at f company Another table contains a list of names such as: Judy Kyle Megan I want to create a field on the messages table that will show Judy and Kyle as associated tags for that message because the strings Kyle and Judy match the entries in the list of names. Appreciate any help! Andy

Well, Here’s the input. I have 2 table Table 1: Clients orders Table 2: Stocking Table. It has {Import Date} field. The question 's for {Sold out (days)} field i wanna calculate the period of a Package sold by the last day sell out (which lookup in table 1) minimus {Import Date}. So which formulas should i use in this situation ? Hope your guide can help!

I have 2 fields Field 1: Date (lookup) Field 2: I wanna if the date (and time) Duplicate , just show 1 value date time . If it has 2,3 different date, i wanna show all of them And i use this formula and it shows error, what am i missing ?

Hello Community! Hoping, yet again, for assistance from a Formula pro. My table has the following look up fields: 1 - {Invite Docs} 2 - {Other Task-Related Docs} 3 - {Calendaring Docs} 4 - {Plan/MTG/After Event Docs} If there is a document in any one of these four fields, I need the Formula to return a value of “Has Docs”. In oher words, records that have blanks in all four fields would not return the “Has Docs” value. I attempted an IF statement, but with unsatisfactory results! :worried: I’m afraid the use of BLANK () is currently beyond my skill level. Thanks in advance for any assistance you can provide.

IF( SEARCH(“1”, {Type of Workshop or Class Offered}), DATEADD({Proposed Date of Workshop or Class}, 0, ‘days’), IF( SEARCH(“2”, {Type of Workshop or Class Offered}), DATEADD({Proposed Date of Workshop or Class}, 1, ‘days’), IF( SEARCH(“3”, {Type of Workshop or Class Offered}), DATEADD({Proposed Date of Workshop or Class}, 2, 'days’), IF( SEARCH(“4", {Type of Workshop or Class Offered}), DATEADD({Proposed Date of Workshop or Class}, 3, ‘days’) ) ) ) I added the last bit by copying the one before it and now the formula doesn’t work, what am I doing wrong?

Hello everyone! Ive exhausted all my options. Hopefully the amazing Airtable Community can help me out… So in essence the formula needs to get words from Column A and separate them with a vertical bar i.e. " | " and if a set of words like “New This Month” exists in column A the spaces need to be replaced by underscores. In column A (Multiple Select) I have e.g: Movie, New This Month, Entertainment I need for column B (Formula) to produce the following with the data in column A e.g: Movie | New_This_Month | Entertainment Thank you in advance!

Is there a way to solve a simplex algorithm in airtable? Similar to solver in excel?

I would like to capture three dates: When a new record was created (Date Added to Accelerator) When the status in “Approval Status” was changed to either Approved / Not Approved (Date Approval was Made) The difference in time between those two dates (Approval Duration) *The format for Approval Duration should be in days and hours. Example “2 days, 3 hours” (Or something like 2 d 3 h, that format isn’t as important as it is to show the amt. of time in days and hours) My concern is that because I’m using two different functions (Created Time and a Formula) that it’s having trouble computing the difference. Please let me know if I need to change of of my existing formulas or what I can use for the Approval Duration column to make this formula work. Thanks!

This closed topic answered my question about accurate time stamps for imported records but i can’t figure out the formula… what is the operator for “not blank”. This is all very new to me and i am learning as i go… IF({Original Time Stamp}=BLANK(),"{Time Stamp}",IF{Original Time Stamp}=ISNOTBLANK, {Original Time Stamp}) https://community.airtable.com/t/created-date-time-field-and-imported-data/30941

In my database of garden plants I use the field WETENSCHAPPELIJKE NAAM, it has two or three strings, the genus, the species and/or the variety of the plant. the variety always between apostrophe’s. I like to create a formula-field which automatically creates a CODE, the first character of each string in the field WETENSCHAPPELIJKE NAAM, without the apostroph. I tried but without the result, can somebody help?

Is there a way that we can automatically calculating based on different conditions For example IF Service: Deep tissue massage Time: 60 minutes Result: Commission: 80 IF Service: Deep tissue massage Time: 90 minutes Result: Commission: 150 IF Service: Pain Relief massage Time: 90 minutes Result: Commission: 140 Thank you so much :pray:

I’m new to Airtable and databases in general. My database will be used to organize a dance competition. I’m trying to construct a formula to calculate how many medals I will need for each competition. The number of medals needed depends on the type of dance and the number of competitors, a few of which are shown in the image below: My formula keeps returning an error (Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.). I have been through many Airtable articles, tutorials, and forum posts, and still do not know what I have done wrong. Any help is much appreciated. IF( AND( OR({Dance} = “Reel”, {Dance} = “Slip Jig”, {Dance} = “Single Jig”, {Dance} = “Light Jig”, {Dance} = “Treble Jig”, {Dance} = “Hornpipe”, {Dance} = “St Patricks Day”, {Dance} = “Humours of Bandon”, {Dance} = “Job of Journeywork”, {Dance} = “Blackbird”, {Dance} = “Garden of Daisies”, {Dance} = “White Blanket”), {Number Entered}>=4), 1, IF( AND({Dance} = “Reel & Set”,

Hi, I’m having issue with calculating meds left. Example, I take PILL-A MORNING/AFTERNOON/NIGHT like this 0/1/1 and after 10 days I will use 20 pills. If I have 40 pills I will need refill after 20 days. PILL-B I will take eg. 2/0/0 and if I have 50 pills, after 25 days I will need a refill. How to calculate this? I have found date for refill but multiplying with ZERO returns infinite. Airtable Grid view - Airtable Explore the "Grid" view on Airtable.

Hello, Is there a way of adding all of the values within a multiple select field type cell? Any help or suggestions would be appreciated!

Hello lovely Airtable Community. I’m building a CRM system - I have full profiles built for leads from Instagram and Facebook in one table. I have just added a new table inside the base and I am sending information from Zapier to Airtable for customer purchases. Does anyone know how I can look up / roll up the information so that the products and the amount spent in the Shopify table show up in my original CRM table? I hope this makes sense! Thank you so much

Hi - I have a column {Count of Amounts Paid of $250+} and I want to divide it by 9 because the number of scholarships available is determined at 1 after every 9 enrollments that are $250 and over. In an adjacent field - but because, for example, 51 divided by 9 is 5.6666666667 I want also want to ROUNDDOWN so that the scholarship fiel doesn’t display more scholarships than are actually available (only when it gets to numbers evenly divisible by 9. For the scholarshps field I tried: ROUNDDOWN({Count of Amounts Paid of $250+}/9) but I get an error message. I imagine my syntax is off in someway but I can’t figure it out. Thanks for any help you can offer!

I’ve been pouring through the other IF threads, and still can’t find a solution that returns the correct results. Instead, I’m getting #ERROR. Here is what I want to do: I need to set a date in the “Next Update Due” field. It should be 35 days from “X” date. I need the {Item Start Date Manual Override} field to have priority over the others, unless {Item Start Date Manual Override} is blank. SO: If {Item Start Date Manual Override} is not blank, then i want to add 35 days to {Item Start Date Manual Override} otherwise If {Last Keyword Insertion Date} is not blank, then i want to add 35 days to {Last Keyword Insertion Date} otherwise i want to add 35 days to {Date Item Added to Air Table} Here are the two versions of the formula as I’ve written them, but it gives me an error unless there is a value in the {Item Start Date Manual Override} field IF( and( {Last Keyword Insertion Date} = BLANK(), {Item Start Date Manual Override} = BLANK() ), DATEADD( {Date Item Added to Air Table}, 35, ‘

I have a Table in which I track all my contacts of organizational partners. I log the date I contacted them, and them I have a frequency that I set for that partner (the frequency can change based on time of year or other circumstances so each record needs the frequency). There are 5 different cycles (3 Weeks, 6 Weeks, Quarterly, Bi-Annually, Annually) and there are three options that I need to return NOTHING in the formula. I believe this is a nested if statement, but the formula is throwing me off because of the date contacted column needing to have different amounts of days to return a specific date in the future. Any help would be appreciated. I’m happy to answer questions. I’ve included screenshot in case it helps at all.!