Formulas

Hello all! I want to have countdown (showing how many days left) based on the stage (a single select field) of a record. Each stage should take a certain amount of time to complete, so the goal is to see how many days left I have to complete a stage. Then once I change it to the next stage, that countdown is reset to the parameters of the next stage. I have parameters are mostly set by formula-based date fields that live in the table. So it would look something like this: If {Stage}=Stage 1, calculate difference between {date 1} & 14 days from {date 1} If {Stage}=Stage 2, calculate difference between {date 2} & 14 days from {date 2} If {Stage}=Stage 3, calculate difference between {date 3} & 28 days from {date 3} If {Stage}=Stage Complete, “Complete” I tried doing a DATETIME_DIFF inside of the If function, but have been struggling with errors. Seems pretty straight-forward, but I can’t figure it out… Any help would be great! Thanks in advance.

I have been using the really helpful formula in this airtable article to have a status of done, complete, and in-progress for my project management base: https://support.airtable.com/hc/en-us/articles/221104988-Conditional-formulas-with-emoji My formula currently looks like this: IF({Done} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due Date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :thumbs_up: In-progress​:+1:”)) However, I would really like to divide the In-progress into tasks that are currently in-progress (keeping status. as is) and tasks that are upcoming (start date is after today, basically). This is what I want: :white_check_mark: Done :white_check_mark: : IF({Done} = 1, “ :white_check_mark: Done :white_check_mark: ” :thumbs_up: In-progress :thumbs_up: : Start Date=before today and Due Date=After today :alarm_clock: OVERDUE :alarm_clock: : Start Date = before today and Due Date=before today :clock2: Upcoming :clock2: : Start Date= after

Hi, I’m creating a table that includes links to external websites like facebook pages, instagram profiles, personal websites, etc. I’d like to track how many people click on each of the unique links. Is there a way to do that? Airtable’s Support team told me this is not a native feature. Anyone have creative ideas on how to do this? Thanks.

Hello! I recently got into canning (and general food preservation). I have created a database which helps me manage my inventory levels of products I have canned (Example: Jars of: Carrots, Green Beans, Jams, Jelly, etc). In my table where I record batch information - I am recorded the date I created that batch of product. I’d like to have a column (called Age) that returns the age of the product (batch) in #.## format. For example: If I created a batch of product today, it’s age should read: “0.00” meaning its 0 years and 0 months old. If I created a batch 1 year ago, I’d like it to say “1.00” because it’s a year old. If I created a batch ‘days short of it being 2 yrs old’, I’d like it to say “1.11” because it’s 1 year old and 11 months. Any help would be greately appreciated.

Hello everyone, I need to add text before each word in an already established Formula column. Example: Formulas Column Results : ENG, SPA, FRA, RUS, ITA I want to add “Title” and a underscore before each text resulting in: Title_ENG, Title_SPA, Title_FRA, Title_RUS, Title_ITA Current formula is: IF({Title (No Special Characters)}, “” & {Title (No Special Characters)}) & IF({Audio Languages}, “_” & {Audio Languages}) Thank you in advance!

ALL ROLLS UP I HAVE CREATED IN JUNE MONTH ARE WORKING FINE BUT WHEN I TRY TO CREATE SAME ROOLUP ITS NOT WORKING BEFORE I USE TO GET OPTIONS NOW I AM NOT GETTING OPENS FOR SUM, AVERAGE FOR REFERENCE I HAVE ATTACHED SCREENSHOTS

Hi, I’m trying to get unique text values using ARRAYUNIQUE but the result is returning duplicate entries. I have a lookup column called airlines with the values: American, Delta, Southwest, Southwest I want the result American, Delta, Southwest. But ARRAYUNIQUE({Airlines}) still returns American, Delta, Southwest, Southwest Any ideas?

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 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?