Formulas

If I want to specify the last day of the current month in a cell as a date, what is the syntax? In Google Sheets & MS Excel it is EOMONTH (End Of Month). EDIT - HERE IS THE FORMULA - THANKS KAMILLE & JUSTIN DATETIME_FORMAT( DATEADD( DATETIME_PARSE( DATETIME_FORMAT( TODAY(), 'YYYY' ) & '-' & (VALUE( DATETIME_FORMAT( TODAY(), 'MM' ) ) + 1) & '-01', 'YYYY-MM-DD' ) , -1, 'day' ) , "MM-DD-YY" )

No matter what I try, Airtable will not allow me to display a latest linked record in the primary field. All attempts end with circular reference errors. Table structure: Company name < primary, formula > ( {current name} ) company names [1:n] < linked > related to Company Name current name < conditional lookup > from {company names} ( {if latest} = 1 ) ... Company Name name < text > company [n:1] < linked field > related to Company if latest < formula > ( IF {start date} = {latest start date}, TRUE() ) ... I’m running out of ideas. As soon as I use company names in conditional context with if latest, I don

Hey there, I wondered whether a formula could be employed to detect broken PDF attachments? Due to an unknown error, some attachments are broken and I’m looking for a way to detect those.

Hi there, I know this question has been asked previously, and I realise there’s resources to give an idea, I’m just new to Airtable formulas, and still can’t make it work for me. My question is super simple. I have a ‘date’ column, and then a column with formula to add 30 days. It then produces an error in all of the cells where the ‘date’ column hasn’t been populated. I realise I need to use ‘IF’… but struggle from there. I’ve tested out examples that I’ve seen, but no luck. Thank you!

I want to use an IF statement in a field where the logic will basically be the following: IF({Calibration Frequency}= Annual, ({Last Calibrated}+365 days)) I know this isn’t a valid way to code this - strictly an attempt to explain the intent. But basically, the {Calibration Frequency} field is biannual, annual, or 2 years. Depending on that value, i want the field to display the correct “due date” in the coded field based on the calibration frequency and the {Last Calibrated} date.

My table lists all the video episodes that my company releases. Each Episode’s Record has an Air Date date field. Each Episode’s Record also has a Previous Episode field that has the Project Name of the last released episode. The last released episode is based on the Air Date of the most recent previous episode. Currently, I manually add the previous episode’s Project Name to the Previous Episode field of each Episode’s Record. I want to use a formula to fill the Previous Episode field automatically. Can you even use a formula to reference a field in a different record from the same table?

Hello, Could you please tell me if you can hide multiple prefilled fields ? When I do it, it only hide the second one (out of 2). I cannot manage to have both hidden Thank you in advance !

Using https://regex101.com/ - the below works well enough for omitting words from sanitation. (?i)(?!keepWord1|keepWord2|keepWord3)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6) So this doesn’t work; REGEX_REPLACE(Player,"(?i)(?!keepWord1|keepWord2|keepWord3)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6)", "Word_Replacement") The below at least does work, but it’s missing the important omission check - so some proper words get flagged and replaced unnecessarily. REGEX_REPLACE(Player,"(?i)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6)", "Word_Replacement") Any ideas of how to formulate this within a Formula Field?

I am trying to create a button to link to a client’s address and am getting a “about:blank#blocked” url when I click on it. What am I missing? The URL Formula I am using is: ENCODE_URL_COM (“https://google.com/map/place/”& {address}) Or is there a better way to accomplish this - it needs to be able to be clicked from the app on a mobile phone. Thanks!

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)

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?