Formulas

In the primary field, I’m trying to create an ID Code for my ancestors and I need some help. An example ID Code is: First three letters of surname, first three letters of name, year of birth eg Denis O’Brien born in 1836 = ObrDen1836 However, I have two issues with this example as the formula has created O’BDen1836 How do I remove the apostrophy in the surname and change the capital B to a lower case ‘b’ to change O’BDen1836 to ObrDen1836? Is this even possible? My current formula is: LEFT({Surnames}, 3)& LEFT({Name}, 3)& YOB I’ve attached a screenshot for reference also. Thank you so much, your help is very much appreciated - and may well save my sanity! :slightly_smiling_face:

Hi ! I got a beginning date : 01/01/2010 I got an ending date : 31/12/2014 In a field, I would like to show : 2010, 2011, 2012, 2013 (the dates included between beginning and end) Any clue on the formula I should use ??? I’ve investigate for hours with no answer… :frowning: A big thank you in advance Alex

Hi everyone, I’m unable to get a DATETIME_DIFF formula to work the way I want it to. Basically, I have a Status field as a single select as follows: My goal is to have a column that shows me the number of Days since a record was created. When I use the DATETIME_DIFF by itself, it works just fine as below, showing 0 days However, what I want to do is to show this only when the Status column is not equal to complete. So I’m trying to wrap my DATETIME_DIFF as the true result when the Status != “Complete”. But this doesn’t work. When I set the status to complete, I get the false result of the IF formula, which is “Complete”, so that part works fin

Hi, Sorry if this is a basic question, but I just can’t seem to figure it out. I’m trying to get a count of all the records within the same table. So for e.g. say I have two columns in a table as depicted below. I’m trying to have the “Count of all Tasks” column be dynamic so that when one more record is added, the number changes to 5 and so on. The reason is that I need to lookup this total count number into another table. However, I’m not sure how to proceed. I can’t seem to use a Rollup or a Count type for the “Count of all Tasks” column because they need me to refer to another table. In essence, I’m trying to rollup a count of all values in the same table. I’m pretty sure this should be fairly easy and I have a blind spot here. Any help is much appreciated. Thanks.

Hello everyone in the Community! Need some help with formulas. We are managing content for marketing campaign and need to find a way to make a report in AirTable. In one table we have all the Post with Post Dates. Another table we plan to use for reports. So I linked all the planded posts to Posts field. Then using Lookup Post Date (from Posts) I took Post Dates. I need to check how many posts been posted on a particular week. I’m setting up a week using Start week date. Can you please help with this? Thank you!

Hi, So I have my formula calculating the 90-day deadline from the start date. DATEADD({Start},90,‘day’) But I want it to only calculate IF there is a date in the start column. Currently, if there is no date in the start column it produces a #error, however, ideally I’d like to set the formula so that the deadline is blank until the actual start date has been entered. Thanks in advance and apologies for the newbie question!!

In [Workers] I have a rollup field called {Lowest_rating} which uses the Min() formula to return the lowest number a worker has in [Worker Interactions]. FWIW, the field in [Worker Interactions] that is being rolled-up is a number field. Where the worker has no rating yet, the rollup returns a “0”. I find this to be visually confusing. Is there anyway in the rollup to return the cell empty rather than a zero? Photos below. thanks!

We track time and often time there are more than one entry until we get a total. Here is what we do… {Start Time 1} and {End Time 1} and calculation formula is DATETIME_DIFF({End Time 1},{Start Time 1},‘seconds’) {Start Time 2} and {End Time 2} and calculation formula is DATETIME_DIFF({End Time 2},{Start Time 2},‘seconds’) {Start Time 3} and {End Time 3} and calculation formula is DATETIME_DIFF({End Time 3},{Start Time 3},‘seconds’) Then I have a Total Calculation formula Field: {Calculation 1} + {Calculation 2} + {Calculation 3} The problem is if one of the calculation fields is empty the Total Calculation field just show “NaN”. I would love any advice on how to solve this or a better way to calculate overall.

Hi, I have data that looks like this: 123: Tom Smith 11: Sally Smith 1200: John Doe I would like to extract just the name with a formula. I know I need to use RIGHT/LEFT, but I can’t quite figure out how to say “extract all text after the colon.” Thanks!

Hi! I’m looking for help with an If formula. I want the end result to be: If Column A says 5, then Column B will display “Now” If Column A says 4 or 3, then Column B will display “Soon” If Column A says 2 or 1, then Column B will display “Later” The formula I used was: IF({Ranking (from Projects)}=5, “NOW :hammer: ”, IF(OR({Ranking (from Projects)}=4, {Ranking (from Projects)}=3), “SOON :soon: ”, “Later :file_folder: ”)) But some of my fields with Column A that says 5, says Later. Do you know what I did wrong? Thanks!

I have an array in a record: “Bob Smith - 30%, Susan Bee - 50%, Alan Watts - 20%” and I want to change this array from percentages to money payouts, in accordance to a currency field, for example total income of $1500. How can I calculate within an array so that the end result would be something like this: “Bob Smith - $450, Susan Bee - $750, Alan Watts - $300” Is this even possible?

Hello, wonderful Airtable community. Not sure if this is a formula or not, looking for some guidance. I’m trying to shorten a list of things that contain sub-groupings by swapping out the full sub-group with a parent word/item. For example, in my shop we sell: Three types of apples: Fuji, Honeycrisp, Gala Two types of oranges: Tangerine, Mandarin One type of banana: Cavendish Within airtable there is a table of the most loyal customers and what they often purchase. A field linked to multiple records. The question/challenge: How would you swap in the group name only if the customer purchases the full set of items? If the list of multiple records contains “Fuji, Honeycrisp, Gala” then swap those out for “Apples”?

I’m trying to create a formula that can search a column that contains multiple text dates and return only the earliest date. My {Launching} column is a text field that contains many launch dates, as well as region codes for what the date applies to like this - AU: 02/28/2021, CC: 03/01/2021, CK: 03/01/2021, CX: 03/01/2021, GU: 06/30/2021, MH: 06/30/2021, MP: 06/30/2021, NF: 02/28/2021, NZ: 02/28/2021, PR: 06/30/2021, TK: 02/28/2021, VI: 06/30/2021 I’d like to create a formula that can search this text field and tell me only the month of the earliest launch My current formula is this: IF(SEARCH(": 02/", {Launching}), “2Feb “,””) & IF(SEARCH(": 03/", {Launching}), “3Mar “,””) & IF(SEARCH(": 04/", {Launching}), “4Apr “,””) & IF(SEARCH(": 05/", {Launching}), “5May “,””) & IF(SEARCH(": 06/", {Launching}), “6Jun “,””) The values returned look like this: 2Feb 3Mar 2Feb 3Mar 4Apr 2Feb 3Mar 4Apr 2Feb 3M

Hi I have this time tracker system in which different stages of our process is time stamped by an automation. The difference between these time stamps are calculated in several columns depending on the task (in minutes). In order to account for breaks (maybe the work not being completed during the day off), I installed the Timer app to calculate that. Once the break is calculated, that is deducted from the total time calculated by the time stamp automation. It works for the most part and the timer app is perfect because you can pause and resume the timer whenever you want. However, after running the timer for a task I couldn’t complete overnight, the calculation between the time stamp automation and the timer’s calculation wasn’t accurate. Only when I typed in the break duration did the calculation work. Is this perhaps due to the fact that my automation calculates for “minutes” while the timer calculates not only minutes but seconds and hours? This usually works for smaller break t

Hi there! I’m fairly new to formulas and have been playing around with using them in a new base for a client. I’m wondering if there is any way to create an IF statement or some other formula for if a Date field is empty? Right now if the date field is marked empty, it showcases an error, but I’d like it to still include the other values if left blank. It would be great if it could maybe have TBD" instead. Any suggestions are greatly appreciated! Here’s the formula I have so far: "[" & DATETIME_FORMAT({START / LIVE DATE}, ‘MM/DD’) & "] " & {PROJECT / PROMO NAME} & " " & “[” & SEASON & "]" Also a screenshot of what I’m trying to do:

Hey all! Got a question for y’all: Say I’ve got three tables. Albums Songs on each album (each song has an environment attached) Environments (this is a finite list of pictures with name labels that are used for reference in the ‘Song on each album’ table) In the album tab, I’m looking to create a field that lists/combines the environment used from the first and last track of each album to send out for reference. The first and last track is always called ‘FT’ or ‘LT’ in the ‘Songs on each album’ table. Any insight here? Found this, but it looks like more of a lateral way to do combinations (and it doesn’t have to be pictures – can just be the names of the environments/pictures, if that makes sense).

Hi There. I have a table for capturing fuel fill ups. In one column we have the opening ODO balance, the other is closing ODO balance. Closing Balance is what will be inputted by the user, the opening balance should automatically then become the same value as the closing balance. Perhaps this image might be a bit more clear of what i want to achieve. Apologies if the explanation is not clear

Hello! I’m populating my base with data from an external API and would like to update it regularly as new results come in without creating new duplicate records. I’ve read a couple of other topics that discuss that (like this one and this one), but am still struggling to figure it out. I’m using the [uri] field in the base records and trying to match them to the array of [uri]s from the api. If there’s a match, the record would update, and if not, a new one would be created. FWIW my knowledge of js is survivalist at best! Here’s what I have so far: // The loop for each result in the API for(let i = 0, l = data.results.length; i < l; i++) { // ids for current records let table = base.getTable("Observation"); let queryResul

I have this formula. If there is no date, the formula errors out. I need it to be the exact same formula but if the date field is blank, it still fills in all the other columns. Here’s the formula: DATETIME_FORMAT({Date}, ‘YYYY-MM-DD’)&" “&Type&” || "&{Fellow}&{External Guest} How would I change it to include blank-dates?

Hi Guys, Need some help, new to Airtable. I am trying to create a formula to update a next due date for monthly rent from a contract start date instead of manually updating the dates monthly. The columns i have is Contract start date and Rent due date. Thanks

Hey community, I am decently new to Airtable and am excited to get to know it better, but I am stumped on creating this formula. I’ve beeI have been trying to output the range of a number of days within a week when given the first day of that specific week. I need it to be formatted with the month like, “June 21-27” in it’s column. What I have is… DATETIME_FORMAT({Start Date}, ‘MMMM DD’) & “-” & DATETIME_FORMAT((DATEADD({Start Date}, 6, ‘days’), ‘DD’) I keep getting error messages like: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text. I’ve been messing around with it quite a bit, but can’t seem to produce what I want. I am sure there is a solution to this simple formatting problem. Is there a way to do this using the “Date” field type? Or maybe I can only use DATE_FORMAT() once per line.

Hi Everyone, I’m new to formula’s and can’t quite work out the right answer. I have job listings that I want to archive once they have passed an expiry date. I currently have a status column that has a dropdown to select ‘archive’, once this is selected it goes onto another view (using a filter) but it would be really useful to automatically move this once it reaches the expiry date’, rather than doing it manually. Thanks in advance!

Good day, Airtable Community, I have a base that looks like this, And I would like to create a formula that displays true (or 1) if that day is today, so if today is Monday, all the fields that contain Monday will be 1. But I cannot find any way to do this with the Today() and other date functions. I strongly believe I am missing something. Can anyone shed some light on this?

Hi there, I’m trying to use the DATEADD function to calculate an end time for an event. My formula is: DATEADD({Timeslot StartTime},{Duration mins},‘minutes’) {Duration mins} can be a lookup field or a formula field. I’ve tried all formatting options for these fields but none are producing the correct output. If I make {Duration mins} a text field, the output is correct. I need to make the formula work with {Duration mins} being either a lookup field or a formula ~ What am I doing wrong? P.S. I have also tried to troubleshoot the issue by toggling the GMT settings on and off, all to no avail. Thanks for your help.

I have a set of product attributes, each attribute belongs to a group, example: Product - T Shirt The attributes would be Size > Medium Cut > Round Neck Color > Green What I would like to do is have ONE multi select column with all the attributes in. The output needed would be split into separate columns like so: Column 1: Attribute 1 name : Size Column 2: Attribute 1 Value: Medium Column 3: Attribute 2 name: Cut Column 4: Attribute 2 Value: Round Neck Etc… The multi select needs to be able to be in any order, I have made another table with each attribute and it’s attribute name and then used a LOOKUP to send that back to the main sheet. This returns a string of 3 attribute names. Is there a way to take the first value in the string and return it’s attribute name? I know this seems like doing it the hard way but it has to conform to something our website can digest, and tha