Formulas

Hello! I have built a :zap: Zap that sends colleagues an email reminder if the deadline for a task is past due. I am wondering if it’s possible to include in the Zap email an Airtable link to the specific record? This would enable the recipient to quickly click the link and be taken directly to the record for edits. Perhaps there’s a formula to add the record’s unique URL? Thanks much! :nerd_face: :surfing_woman:t5:

Hey guys, I have hundreds of records that contain one of 12 combinations of initials. I want to create a new column that finds those initials and depending on what they are, write the entire name. For example: Column A includes: 2017-02-07.boe_.ag_.pdf 2017-02-07.bos_.ag_.pdf 2017-02-09.bof_.ag_.pdf I want the formula for Column B to find the initials, and write in the full name following these rules: if it finds “boe”, write “Board of Education” if it finds “bos”, write “Board of Selectmen” if it finds “bof”, write “Board of Finance” So, the end result in Column B would be: Board of Education Board of Selectmen Board of Finance Thanks for any and all help with this! If cell contains A, write X - or - if cell contains B, write Y

Hi, I have a formula DATETIME_DIFF(Date,28/06/2020,‘days’) This should give me the number of days between “Date” (a date field) and a specified date. I must have something wrong as it displays 18441 regardless of the date I specify. What do I need to do? Cheers Jason

I am trying to write a string of IF statements…so the logic is this: If user selects "Country’ as “North Vietnam” and/If the ‘Ultimate Destination’ is “Long Beach”, then the result should be the date column{North Vietnam to LAX}. This works. But… The problem is it won’t let me keep adding to the IF string. for example below: it won’t recognize the "China’ Query. I am really struggling with this one…any help would be so much appreciated. IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX},IF({Country}=“South Vietnam”,IF({Ultimate Destination}=“Long Beach”,{South Vietnam to LAX} IF({Country}=“China”,IF({Ultimate Destination}=“Long Beach”,{China to LAX},IF({Country}="India ",IF({Ultimate Destination}=“Long Beach”,{India to LAX})))) This formula works, but I cannot add onto it: IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX}))

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

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 i

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.

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”, {Danc

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

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!