Formulas

Hello community! Here’s what I’m seeking assistance with: I have 3 fields total: Length of Service, Service Start Date, and Service End Date (or anticipated) I would like to write a formula for the Length of Service field that does what I currently have, but takes it one step further. Currently, I have a simple DATETIME_DIFF formula to display in Years the difference between the two dates. However, I would like to use today’s date (I’m guessing the NOW function) IF the Service End Date is in fact an anticipated date in the future. So if the Service End Date is greater than today, I would like to use today to calculate the time difference, and if the Service End Date is in the past, I’d like to simply calculate the date difference between the two. Any help is greatly appreciated!

This formula worked fine, until we brought on an additional team member in another country. I am now tracking this data for 3 team members in the US, UK, and Germany. I think I ultimately have 3 issues I’m trying to resolve: I’ve seen the great U.S. and U.K. Holidays tables created by user W_Vann_Hall, but my company has a number of additional days off outside of federal holidays I’d like to include. I’m going to go ahead and assume there is no easy way to do this. I don’t see one for Germany, and I’m not up for building that myself. But would it be possible to alternatively upload .ics files with the holidays for each company into a single table (in ISO-Format), and link those records for each team member? Assuming #1 above is more advanced than I’m capable of at this time, and #2

This is obviously very much related to my last question, but have a formula that is calculating the average score (0-5) from multiple judges. I’d like to create a Kanban view based on that average and I’m trying to figure out the best way to do that. I can round off to the nearest whole number, but Kanban requires a single select to create a view. Is there a clever workaround that would let me accomplish this?

I have a situation where I need to display the linked records in a field like this (including the double “” on either end): [““recFMVIujI5jqSiSD””,““recf8rGUjcM0li5f4"”,”“recztuGU7Tl4GSSYU”"] Is this possible? I have this code to format it, I just need the ID part. '["' & SUBSTITUTE( {Relations}, ', ', '","' ) & '"]' Which displays: [“record1”,“record2”,“record3”] Instead of the record primary field name, I need it to display the record ID in a text field as formated above. I have a linked record column, then I added a new text field column that grabs the name from that linked column but I need it to be the record ID. Does that make sense? (replace the name with the record ID in the brackets)

Hello - We are creating a way to track social media posts for our clients. Clients will ask us to make a certain number of social media posts on their behalf, we then want to keep track of how many social media posts have been made and how many are still required for each client contract. Here are the two tables we have created Social Media Posts Created - Airtable - Content overview Social Media Contracts (requests) - Airtable - All content and requests I’m new to Airtable, so this may be a simple formula, but any help would be appreciated.

I’m having trouble even getting started. I know how I would do this in a real programming language, but I’m not sure how to use an airtable formula. I have a lookup field that is getting rankings (1-5) from another table. I’d like to average those rankings. The dumbed down version is just to average entries in a multi select. I would expect to be able to use COUNT() and SUM() for that but I’m not getting anywhere.

Hello there, I am trying to extract the very last email that is input into a field. Example of data would be: godzilla@gmail.com, mouse@gmail.com, waterbottle@gmail.com I want to grab the very last email no matter how many emails are input in the field. I have tried using a combination of RIGHT() and MID() but so far haven’t been successful. Thank you all in advance!!!

Greetings Airtable Community, I am seeking help with formatting the formula below that combines data from two multi-select fields: {College}&" and "&{Department} Example of what the formula returns: Humanities and Department of Religion {College} is always be populated, but {Department} is sometimes empty. When this is the case, the formula returns: Humanities and Is it possible to edit the formula so that if {Department} is empty, the " and " will be removed, returning just the name of the College? Thank you very much!

Hi everyone! Kindly asking for your help for formula in airtable. I’m trying to keep track of our team’s first response time and completion time duration for our Airtable tickets but we would like to exclude the count for non-working hours, weekends and holidays; our working hours will only be from Monday to Friday, from 8:00AM to 5:00PM GMT +8. Currently, I’ve already set it up like this: First Response Time duration in minutes DATETIME_DIFF({First Response Time},{Opened date & time (PHT)}, 'minutes') and Overall completion Time duration in minutes DATETIME_DIFF({Resolution Time},{First Response Time}, 'minutes') This is it looks like at the moment. Hoping for help from anyone! thank you in advance!

Hi, on “IiloscZlotowekTydzien” i have formula =iloscZetonow*2. Field iloscZlotowekCalosc I need to update each week by adding iloscZlotowekTydzien to iloscZlotowekCalosc. Any idea how to do it?

I cannot edit any of my formula fields. I even created a new database in a new workspace just to see if there was some sort of reference error in my existing database. No luck. In all instances the formula field is simply greyed out / inactive. No error messages. First time poster. The same question was posted in April but it was never resolved and the thread is closed. Anyone know of a solution?

Hey awesome community! Working on a new setup and cannot wrap my brain around something. I have an inventory management setup, with a table for all Inventory items and a table for Purchase Orders. As items are received I would like the amount to add into the “Quantity On Hand” total…but only once. Using the 12ga Sheet Aluminum as an example, I would like the On Hand to update to 10 units with todays (06/30/2022) received order, but I don’t want the formula to look at the received amounts every time…just pull it in the one time. We do a yearly inventory to update what we have and I don’t want to worry about the formula confusing things. I hope this makes sense to someone who can help make it make sense to ME!

Hi. I’m looking to track how much time (in days) a lead spends in each stage in a pipeline. I’m basing it on start and stop dates of time in stage. The formula I’m trying to accomplish is this (hope it makes sense): Is the start date blank? YES: Do nothing, stay blank. (end) NO: (ask) Is the end date blank? —1. YES: Subtract today’s date from start date —2. NO: Subtract end date from start date I came up with the following but I just get an error that the formula isn’t correct. IF({Start Date} = BLANK(), “”, IF({End Date} = BLANK(), DATETIME_DIFF(TODAY(),{Start Date}, ‘days’), DATETIME_DIFF({End Date}, {Start Date}, ‘days’) What’s not working?! Definitely new to AirTable and formulas in general so if there is another way to approach this, I’m all ears! :). Thanks!

I have a concatenate function in a table’s primary field. One of the fields it combines is a rollup of companies in a Companies table. Typically, it’s one company per record, occasionally two. To strip the pesky quotes I get when there’s comma in a company’s name (e.g. Habor Lights, Inc.), this formula only partially works: SUBSTITUTE({COMPANIES}&"", '"', '') When two companies are rollup up comma-separated, the formula strips that comma which is in fact needed Company 1: Toyota Research Institute Company 2: Abbot Ventures Rollup: Toyota Research Institute, Abbot Ventures With formula applied: Toyota Research InstituteAbbot Ventures Can I improve on this formula or is there a better approach altogether? Edit: tried the in-rollup approach suggested in

Hi, I can’t find a solution to this: I have a Lookup field that consists of multiple text values separated by a comma. I’m adding a seperate column to indicate the status based of that Lookup field which should contain of 3 possible values: IF FIND “Paid” = Completed IF FIND “Pending” = Pending IF FIND AND “Pending” & “Completed” = Partially Completed Now it seems that AND doesn’t work in combination with FIND. Has anyone found a solution for this?

Hello! Thanks in advance for any help. I am trying to make a formula that will extract a text string from a URL. In this case, the URL is a playlist on Spotify, and the text string is the playlist ID number. Spotify

I have a formula that needs data from the record beneath it in its view. The next record in the view could be arbitrary, so this formula should look for the record below the current record for the values it needs. In a spreadsheet, this would be easy enough, just select which cell I’m targeting. However, this is tricky because this is dependent on the view configuration. Switching to a different view or changing the sort order would change the value of the field. Is there a way to do this?

I am showing a database for timesheets. We collect data by day and by hour. But our administrative department processes the information by period. Sometimes some employees have outside contracts and so their hours change. The number of hours for a period goes from X to Y and back to X. For the moment, each case is automated according to conditions. (Information coming from the employers) But good boss, sometimes wants to change the total hours of a period directly and that all applies to the period. In the example, he wants to change directly the 70 and not the 7… any idea ?

Hello All! I’m using a formula to concatenate different dates an area was worked on as the record ID. In one tab I have all the offices and all of the associated data with them, and in the other I have a submissions page that’s linked to the office page. My problem is some of them concatenate as “AreaName” - Date and others are AreaName - Date. I have attached some screenshots for a visual. Thank you for your help!

Hi! I am trying to build a formula but I haven’t quite found the solution in the community. I have a table with different customers information, with fields such as “name”, “surname”, “email”, “phone”, “company”… I would like to create a separate table in which I can count how many customers I have from the same company. So let’s say I have: Table 1: customer 1 — Company A customer 2 — Company A customer 3 — Company B customer 4 — Company C customer 5 — Company C customer 6 — Company C In table 2, I would need to show: Company A — 2 Company B — 1 Company C — 3 I have the company field built as “single select”, since I work with a limited and specific list of companies. Not sure this makes any difference in terms of formulas. Thanks a lot in advance!

Hello Community! I am trying to figure out the following with a nested IF formula- returning a value based on the name of the project. I have 8 different projects to categorize into one of 3 categories. I keep getting an error message saying to check the formula - what am I doing wrong? IF({Project}=”Hours”,“Personnel”, IF({Project}=”Sales”,“Finance", IF({Project}=”Marketing A”,“Finance”, IF({Project}=”Marketing B”,“Finance”, IF({Project}=”Expenses”,“Finance”, IF({Project}=”Diversity”,“HR”, IF({Project}=”Life Event”,“HR”, IF({Project}=”Executive”,“HR”,"Unknown”)))))))) Thank you!

Hello community, my record has a date and looks up a roll-up like the below example. I’m trying to figure out the right formula to extract the string below: Rollup: …5. 04.05 Vila Falo,6. 05.05 Alvero,8. 07.05 Gjirokastra… Record date: 05.05 Current formula: MID({Rollup}&“”,FIND(DATETIME_FORMAT(Date,‘DD.MM’),{Rollup}&“”)+5,XXX) Output: Alvero,8. 07.05 Gjirokastra… Desired output: Alvero XXX is the part where my monkey brain fails to come up with a solution to determine the length of the string to be extracted. Your advice would be appreciated. :slightly_smiling_face:

Just started working with a dental lab and I’m trying to create a base for targeted outreach to dental offices. After being out in the field today I have entered my notes and assigned a rating to each office. I would like to use 4 stars or 5 stars ratings to trigger an email automation to respective teammates about these particular offices.

Hi!, I am trying to use the REGEX formula, but I cannot use it propertly. I need to extract names from a string. To make it easier the names are written after a hashtag #. This are examples #TOM write the essay of a client Complete the packing list for #Peter From an old post I managed to use this formula REGEX_EXTRACT({Transaction Name}, “#[^\s]+”)) written by Justin Barret This formula kind of works, but is giving me the answer with the hasthag in front #TOM #Peter I needed it without the hashtag. Any clue about how to do it? I am getting lots of trouble to understand the sintaxis of the REGEX formulas. Thanks!!

Is it possible to round up those really long decimals? Thank you!