Formulas

Hello everyone, I am trying to use workday_diff function to calculate whether my team is able to process orders within specified TAT or not. and hence needs to calculate workdays. We have a column that tracks date created ({Date Created})for any order and another column that tracks last modified time to track how much time we took to finish the order ({Date Completed}). Hence the formula is : WORKDAY_DIFF({Date Created},{Date Completed}) But the output counts weekends too and the output is same as using DATETIME_DIFF. Please help me figure it out.

i’m having a lot of trouble to expand to see my formula field. i tried to reload the page, reopen the airtable, and the formula field still tiny. Only using the scroll to see what i am doing. Help! :cry:

Hi - I’m trying to track spending for various entities by date, to allow the creation of a chart of spending by month. Our database has a list of Advertisers. And each of those Advertisers is linked to many Advertisements. Each Advertisement has already been assigned a dollar value and each record also has a date. How would I create a chart for each advertiser for what they’ve spent in a given date range — ideally by month and/or quarter? I’ve been able to create a cumulative sum of ad spending per Advertiser, but I’m stumped on breaking it down by month. Any help much appreciated! Thanks!

Hi there, Not sure if anyone can help me out on how to write the following formula: I have a column with Template ID’s that should be translated to what type of Template they are. I assume i need to write an IF template ID = 1 2 or 3 then show ‘thisword’ and if template ID = 4 5 or 6 then show ‘thatword’ but i have no idea how to write that formula. Im new to this so any help would be hugely appreciated. Looking forward to solving this!

Thank you in advance for your help! I have two columns with dates related to when students passed quizzes. For those that pass Quiz 1, we have a date and nothing in the column for Quiz 2, which would be a second attempt to pass the course. I’d like to create a third column where it contains the pass date from either Column 1 or Column 2. I’m not finding the right combination for a formula. I’d appreciate some help. I’m also not sure how to write a value for saying that any date is ok, and that any cell that doesn’t have a date can be skipped. I’ve tried something like this but it’s totally wrong, and I’m missing how to write any date rather than specifying an exact date too. IF(OR({Quiz 1: Date}=“Date”,{Quiz 2: Result}=“Date”),“Date”,“Date”)

Hello! Is it possible to create a roll-up based on the value of another field? Conditional roll-ups won’t do the trick, because I’d like to the value to be ‘variable’, and not fixed. Is this possible with a formula within the aggregate field? Or any other suggestions? I guess the needed functionality looks a lot like “sum-if” from Excel? Thanks! Dennis

I’m looking for a formula that knows how to filter one field from another field and find what’s left. Would appreciate help

I’d like to have a simple form where people can submit key words, separated by a comma. When they hit submit, they will be emailed all of the submissions to date, including theirs. Is this possible? It will be mostly words, but maybe some numbers too. Submitted as example: dogs, cats, fish, horses - Who can help? @Noamsay

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’t get the value into the primary field. It also doesn’t matter if I evaluate current name in the Company table as shown in the example or in the Company Name table and use a normal unconditional lookup. It can’t be that you have to use an automation for such a trivial thing!? I would be happy if someone knows a solution.

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 isn’t possible, is there a way to double nest the IF statements in my current formula to include 3 sets of holidays? Here is the formula: IF({Date Contract / Redlines / Feedback Returned to Stakehold

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!