Formulas

Looking for a way to quickly find the last record. I can do this in a view with a filter, just not sure of a formula for the newest record. Really what I am looking for is a way to find a record older than 60 minutes. This is a dispatch log, when there is no communication between a security officer and dispatch within the last hour. Dispatch is required to radio the officer and make contact to be sure everything is alright. Or any idea to show the last record, or only show if it is older than one hour. There are many cars patrolling, and a ton of communications. This makes it difficult to find the last record for all patrol officers. Looking for any ideas.

Hi everyone! Looking to create a formula that will show the status of requesting copy. I’d like it to cover the 3 fields I have (all checkboxes) so when a team is done providing a component of copy, they check the box and the status appears. Field 1: Copy Requested Checkbox Field 2: Ready for QA Checkbox Field 3: QA Approved Checkbox Hoping the formula can mirror this: IF Copy Requested is checked then the Status is Copy Requested IF Ready For QA is checked then the Status is Ready for QA IF QA Approved is checked then the Status is QA Approved I have it figured out with just one field in the formula, but to add multiple is where I’m getting stuck. Thank you in advance!

I thought this was simple, but for some reason, it’s not working, no matter what I try. I am trying to set up a formula field that looks at three specific fields in my table and if these three fields are populated and not empty, the formula field should fill in the word “Tagged”. If one or more of the three fields are empty, the formula field should stay empty as well. What am I doing wrong here? IF( AND( {WRITER(S)} !=BLANK(), {PUBLISHERS} !=BLANK(), {GENRE + SUB GENRE} !=BLANK() ), ‘Tagged ‘,’’) Thank you so much in advance.

Is there a formula for checking a image file size? Using a IF statement, when a image size is over 5MB (I put 4MB for the sake for example), then the formula will return as a text “ERROR 5”. I tried something like IF(LEN(Image) > 5, "ERROR 5") to no avail.

Hi all! I’m looking to create a formula along the lines of: If Field(Dropbox Link) is Empty and Field(Review Link) is NOT Empty THEN mark the status as “ :x: Incomplete :x: ” If Field(Dropbox Link) is NOT Empty and Field(Review Link) is NOT Empty THEN mark the status as " :white_check_mark: COMPLETE :white_check_mark: " If Field(Dropbox Link) is NOT Empty and Field(Review Link) is Empty THEN mark the status as :white_check_mark: COMPLETE :white_check_mark: " If Field(Dropbox Link) is Empty and Field(Review Link) is Empty THEN mark the status as “ :x: Incomplete:x:” I’ve tried IF({Dropbox Link} =BLANK(), “ :x: Incomplete​:x:” ) which works, but when adding anything onto that, I hit a wall! Would really appreciate some clarity on this if possible!

Is there any way to shorten this formula?

Hi All! I’ll try to describe my situation. I’ve got 3 tables: Team Members, Project and Roles. Each role is linked to a project and a team member. Each role contains, as information, a start date, an end date and the number of hours / day. For example, Tommaso will be lead developer on project one from 01/03/2021 to 12/03/2021 with a with a commitment of 4 hours/day. Tommaso will also be Sw Architect on project Two from 1/02/2021 to 4/03/2021, with a commitment of 3 hours/day. Here’s the question: is there a way to show the daily workload on a calendar view or a gantt view? For each day I would like to see the sum of the H / days on the various projects, for each team member. Something like that (even without colors). I hope I have explained Thank you all!

Hello everyone, Unfortunately, I’ve a problem when I CONCATENATE(Montant HTVA) As you can see, I have decimals that appears from nowhere :frowning: I need to convert my prices into string to be able to generate an invoice that include all my items lines. How can I solve that problem?

Being no formula expert, I am assuming that the new REGEX_EXTRACT formula function would be a good way to achieve the following. I want to extract three digits from a name field. These three digits are always in the same place after an underscore. Name Field: DAM301_010 POWERHOUSE Extraction Formula Field: 010 Name Field: DAM204_115 DROP THE BALL Extraction Formula Field: 115 I apologize if this seems extremely simple and obvious to the pros, but for a formula layman, it’s anything but. :winking_face: Thank you so much in advance.

Hi all, I’m facing the following challenge: I have a table with a list of Product items (Catalog), to simplify the question I have 3 columns: Product name | Product ID | Sizes e.g. Red Tshirt | 14828 | S,M,L or Blue boots | 5423 | 8,9,10,11,12 I need to take a single row and duplicate it & split it per each size as the following: This original row: Red Tshirt | 14828 | S,M,L Should become this: Red Tshirt | 14828 | S Red Tshirt | 14828 | M Red Tshirt | 14828 | L (So all the other columns have the same values) Has any 1 ever encountered something like this? Thanks :slightly_smiling_face: YV

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 fine. But when I change the status to anything other than “Complete”, the formula shows me a blank. I can’t figure out why this isn’t working. I tried swapping the IF formula for a SWITCH, but I have the same issue. The DATETIME_DIFF doesn’t show a result. Edit #1: I forgot to mention that I’ve also set a (native Airtable) automation such that whe

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 have been scratching my head on this one for a few hours. Would really appreciate any guidance. Thanks in advance.

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 3Mar 6Jun 2Feb 6Jun 3Mar Is there a way to tell the formula to return the text

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 time

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: