Formulas

Hi there, Newbie here, so apologies if this has been asked. Scenario: I have 2 tabs (1. PROJECTS 2. TASKLIST), and I’ve linked each individual task items to a project on the TASKLIST tab manually. Lets assume the link between task-projects from TASKLIST tab is the main source of truth. Question - is there a better way to summarize the total count of tasks per project on the PROJECTS tab? Currently, I have to create two fields in PROJECTS tab. (1) task - to link all tasks connected to the project (2) total task count - use the count formula to add the count in task field. With this approach, I have to manually add each tasks to the task field on the PROJECTS tab, which seems repetitive since data is already on the TASKLIST tab, and also I might accidentally make a mistake if this is manual. Is there a way to lookup TASKLIST tab, and see the relation between task vs project on that tab, and do the count automatically in PROJ

Hey guys! I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A” CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous}) Thanks in advance!

Table “Companies” is a list of companies. Table “Posts” lists blog posts, and has a Date field. They are linked related between Companies’ “Posts” field and Posts’ “Company” field. ie. Posts relate to companies. On the Companies table, I am able to successfully show a “Total Posts” count of the number of corresponding posts, by adding a Count field for the Posts table. However, how can I add another column to Companies which only counts those Posts whose Date fields fall within the last seven days? tl&dr - How do I count the number of linked records whose date falls within the last seven days?

Hi all I’m trying to find a way to record, within a table, an ongoing “history” of when a checkbox has been checked/unchecked. The first stage is simple enough, e.g. a formula field that outputs today’s date when the box is initially selected - IF({Yes/No}=“1”, DATETIME_FORMAT(TODAY(), “DD/MM/YYYY”), “”) but what I then want is an additional field that keeps a kind of tally of when the box has been checked/unchecked, i.e. “Approved 03/07/2020; removed 10/07/2020; approved 13/07/2020…” etc. I think it will probably involve using a “last modified” field but I can’t work out how to make the “tallying” part of the formula, rather than just updating each time there is a change. Can anyone advise if this is possible to do? Thank you!

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! Th

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

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

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 ?