Formulas

Hi,I have created a prefilled Airtable Form.Whenever I click on the link from my Airtable Base, it opens the form and prefills the form with all the desired information (including line breaks, see image below).The trouble begins, however, when I try to use this exact same URL in an Email Automation (see image below).The link is displayed properly in the email (see image below).However, the linebreaks no longer work (see image below). This makes the text unreadable. It's an important part of the process, so if anyone can help out it's very much appreciated!

Hi,I have a multiselect field containing topics. It is a linked field to another table and each topic might contain a comma within its value. I need to prefill a form with that data but I find Is it possible or do I have to remove the commas within the field values?Example of topics:Agricoltura, Ambiente e Territorio, Calamità NaturaliPolitiche per il Lavoro, Formazione Professionale e Servizio CivileCulturaFormulas I tried:This only fills the first topic: CONCATENATE( IF( "Prefilling [Form Preferenze] view in [Contatti] table in base with id 'appYSPQbVoKOqpys7'", "https://airtable.com/appYSPQbVoKOqpys7/shrVvuD1tVKS88IiG" ), CONCATENATE( "?prefill_" & ENCODE_URL_COMPONENT("Temi di Interesse"), "=" & ENCODE_URL_COMPONENT({Temi di Interesse} & "") ) ) If I try to remove the comma+space, it doesn't work for the topics that have that inside them: "&prefill_" & ENCODE_URL_COMPONENT("Temi di Interesse"),

I'm trying to automatically populate emails depending on team member names.So if "John Smith" then in this column "john.smith@email.com" ""And if "Kate Jones" then "kate.jones@email.com" ""etcetcetcfor about 30 team members. I can get the first formula to work but how do i do it for multiple? Tried this but didnt workIF({Name} ="x", "email x"IF({Name} ="y", "email y", ) )

I have 5 non-value fields that indicate if a person has paid an application fee or not. The fields are: 180 GBP, 210 GBP, 240 GBP, 270 GBP, and 300 GBP. If a person has paid, it will say "Completed" under the column that they have paid. It will say "Not Started" if they have not paid. For example: If person A has paid the fee of 240 GBP, it will say "Completed" under that column, and it will say "Not completed" under all of the other monetary columns. (it was not my idea to organise it this way, it is just the way it has to be!)I would like to add a formula field called "Paid" that can identify if a person has paid (Yes or No) based on the statuses of the 5 monetary value columns. I want it to recognize that if 1 of the columns in a row is "completed" that the "Paid" value will be "Yes."Does anyone know if this is possible? Please see photo attached for an example of how the fields are laid out, and a sample "paid" field. 

Greetings, I have a zip code field that's either 5 digits or 9 digits. I need a formula to display the zip code as either 12345 or 12345-6789 depending on the content of the field. I know How to write the beginning of the formula, I do not know how to write the if 9 part.  I have IF({Zip Code} <6,{Zip Code}, Need to describe the 9 digit code with a dash.)Anyone have a thought on this? I am using a number field for the zip code. Is this the correct field to use?Thanks, Scott

Is there a way to put parts of a formula (while writing or editing it) on separate lines, like in a visual editor, to better organize? It works when I cut and paste, but I can’t figure out how to do it in the box. Any suggestions?

Hello, I am having issues with adding additional advertising week options to the formula below. This formula works well:IF(AND({Advertising Plan}, {Live Date}),SWITCH( {Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"52 weeks", DATEADD({Live Date}, 364, 'days'))) But as soon as I try to add additional week options (I have already added them to the Advertising Plan single select field), I get the 'formula not valid warning'. This is what I am putting in:IF(AND({Advertising Plan}, {Live Date}),SWITCH({Advertising Plan},"2 weeks", DATEADD({Live Date}, 13, 'days'){Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"8 weeks"({Live Date}, 27, 'days',"52 weeks", DATEADD({Live Date}, 364, 'days' ))) Please let me know where my formula is mucking me up, I feel I am going cross-eyed after looking at it for so long. ThanksP

Hey there!I am an Airtable novice looking for formula help. 🙃I have a table with columns: Year, Candidate, Entity/Org, Side, Race Level, State, DistrictRight now, my formula is:IF(Candidate !="", CONCATENATE({Candidate},", ",State," ",District),CONCATENATE({Entity/Org}," - ", Side))which will pull the Entity/Org & Side when the candidate field is empty. However, there are instances wherethe District field is empty (in which case I want to pull in the {Candidate},", ",State," ",{Race Level})& instances where the District field AND the Candidate field are empty (where I want to pull in the {Entity/Org}," - ", Side))Any help is greatly appreciated!Thanks!

I know how to make my date show correctly using DATETIME_PARSE(DATETIME_FORMAT({EDate}, 'MM/DD/YY'))And I know how to create a new string by using this formula{EDate (from Events For Sync)}& "- "&{EID#}But what I can't figure out is how to make the first formula work with the second formula into one formula that works.I've tried different ways but nothing seems to work and I am stumpedThis may seem easy for some - but I am frustrated that the date field when you have it set for one way doesn't carry the content over when used in a formula.  But whatever.

Here's the story:A few years ago I set up a base to capture birthday information for school applicants. Users would fill out a form (everyone using it was on EST), and enter a birthday (date only, no time).A year or so ago I started getting reports that some dates shown in our dashboard interface were off by 1 day. E.g. if the entered birthday was Dec 10, the interface would show Dec 9. (This may have correlated with when Airtable made some changes to the way they handled dates/timezones internally?)I'd like to fix this "correctly", so I'd really like to understand what is happening behind the scenes.When users submit a date with a form, does the user locale or browser timezone have any influence on the submitted date? Did the behavior of form-submitted dates change when Airtable made date handling changes?Are dates still stores as ISO strings (with timestamps) even if we don't want/have any time information?How can I correctly render a date submitted with a form that isn't potentially

When a person joins our company I record their actual start date. However, their annual contract renewal date always starts the 1st day of the following month.I want to be able to record their start date and then have a column showing their next annual renewal date (the first of whatever the following month is) that updates as years go on.Example) Someone starts on 11/15/2023. I want the renewal column to show 12/1/2024.  Once the present date is past 12/1/2024, I would want that to automatically update to 12/1/2025.

I need to add up the total days of sick leave, as well as the total vacation leave. This way I can track if they have reached the maximum # of leaves. Can someone assist me?

Let's say I have a table of Invoices and then a table of payments. Each invoice is an aggregate of payments i.e. one invoice can have multiple payments. How can I get the index of each payment in the list of the invoice?

So I'm tracking vehicles in my base(s), and I typically use a formula for my primary field for ease of search for my coworkers:  CONCATENATE("Branch #"," ",Location," ",Year," ",Make," ",Model," ",VIN) Ends up yielding something like Branch #123 2022 Ford F-150 1FTCR15X0TTA01050 This works really well for us, because we use that primary field in a wide variety of forms and other linked bases, so if someone doesn't know their vin, they can search by branch and narrow it down, etc. What I'm trying to get my head around is how to (if possible) reference a single selects selection in an IF statement re: that primary field?  An order to request a new vehicle adds a new line to the base, and as we process the order at that point we add in make, year, model, etc.  Until we do that the concatenate formula just returns "Branch #123" because the other fields are blank so its hard for folks to track their orders and see any inf

Hi there, I am so ashamed to ask this but I could not figure this one out by myself. I have 2 simple text values and an autonumber. I would like to create a formula where i can have these information but not completely. Ex:  1st vlaue is JOHN Second value is DOE and autonumber 333I would like to have  the result 33JODO Thank you for your help. 

Hi,I'm terrible at the formula stuff, but basically I have a Publish Date in my Airtable view for when a piece of content should be published. I want to add the Day of Week as well so we can clear see which post is which day, as well as the actual date (as some gets scheduled a bit further in advance)The formula I'm using to display the day is:  DATETIME_FORMAT({Publish Date},'dddd')All I need to do is display nothing if there is no publish date set

I have a column with the title 'Country', and I am trying to add a new formula column which identifies the Country and creates an entry for the Region automatically. I can get the first IF function to select one region, but when I try to add the second, it fails. Here is what I have for 3 (of the shorter regions) of the 8:IF(REGEX_MATCH({Country}&"", "Belize|Costa Rica|El Salvador|Guatemala|Honduras|Nicaragua|Panama"),"Central America")IF(REGEX_MATCH({Country}&"", "Canada|Greenland|Mexico|Saint Pierre and Miquelon|United States"),"North America")IF(REGEX_MATCH({Country}&"", "Argentina|Bolivia|Brazil|Chile|Colombia|Ecuador|Falkland Islands|French Guiana|Guyana|Paraguay|Peru|Suriname|Uruguay|Venezuela"),"South America")Thank you.

Hi - I have a table with a list of cash flows, dates. These are linked to another table which contains individual deals. I want to calculate, for each deal, the IRR - in Excel this is using the IRR function which takes a list of dates and a list of cash flows and returns a single IRR number. Can this be done in Airtable? Rollups seem to only allow calculations involving one linked field only - whereas I need two: both date and cashflow. Also, there doesn’t seem to be an IRR function. The calculation is relatively straight forward, can I code my own function to support this? Secondary question - right now I have to manually for each date/cash flow record link it to a given deal. Is there a way to link all cash flows where dealID equals x?

Hi folks, I was able to extract initials from full name (thanks to the Name demo base from @W_Vann_Hall ), but because of duplicate names, I added the email as part of the customer identifier (full name + email), which lives in my Customer table. Now I can’t seem to figure out how to extract the initials from this new field. When I try to apply the same formulas to the Name field linked to my Customer table, the formulas no longer work (because I guess it’s merely a linked record, not actual value?). So I created a second table called New Name Table, and extracted the initials under that table first, then linked it back to my original table. But the values are not populating when new records come in (i.e. blank records in the screenshot). FYI the field “Calculation” is the first initial formula applied on the Customer Name field. I am completely lost on how to trim everything up to " (" from the right. Otherwise that would be a solution too (and spares me an additional table). This is

Hey folks,I am currently working on a formula to compare the current time against a timestamp that's populating into my table from a separate service.I am also attempting to create multiple columns that iterate on my formula by comparing NOW with DATEADD +5 "hours" to check if NOW is 5 hours since the time in the timestamp.Having some trouble figuring out what I'm doing wrong with this formula and hoping someone can point me in the right direction:  IF( DATETIME_FORMAT(NOW(), "MM/DD HH:SS") > DATETIME_FORMAT(DATEADD({Timestamp}, "MM/DD HH:SS"), +5, "hours", "MM/DD HH:SS"), "yes", "no")  

Hello! I am working with a formula that is computing a rate for each area that is being updated from a submissions tab; however, not every area has data yet, so Airtable assumes 0. This means that in the future records we get the output NaN as we are dividing by 0 at the moment. Is there a function or if statement that can be used to detect if there’s and NaN output, and if so, set it to 0? Thank you!

Hello! I'm slowly learning how to use date formulas, but I have hit a wall in my ability to troubleshoot.I have two date formulas causing me trouble.1) My Rush Due Date is displaying #ERROR! when "Rush" checkbox is not checked.DATETIME_FORMAT(IF(Rush, WORKDAY(Created, 4, '2023-11-11, 2023-12-25, 2024-01-01, 2024-02-19, 2024-03-29, 2024-05-20'), ""), 'LL')2) My "Time Since Ready for Pickup" formula is displaying NaN when a row is not yet "Ready for Pickup"IF(Completed, "Completed", DATETIME_DIFF( NOW(),{Ready for Pickup Timestamp}, 'days'))Are there any heros out there who can help me out?Thank you so much in advance!Devon

Hi everyone, I searched through the community posts but could not find anything.Using a formula I created custom URL for every person in our company that will allow prefills in a form.CONCATENATE('https://airtable.com/appxxxx','?prefill_Your Name=',Name)for example 'https://airtable.com/appxxxx','?prefill_Your Name=',John Doe)Problem is that there are now blanks and therefore only the first part of link is recognised as such. I know that I can use %20 is used in the final URL. But this does not make the URL in my dataset clickableAnyone an idea what I would need to do? Thanks for your help

I am using Airtable to track hours worked versus workable hours in a month to try and calculate how much overtime there was in a given month. The table below is correctly doing what I want where # Hours Worked is the number of hours a person logged in a given month for different tasks. Total Hours is work days including holidays, Total Work Hours is work days not including holidays. And Hours Worked - Holidays is self explanatory (we don't want to count holiday time towards comp time). Looking at the below screenshot, the comp time would be 1 hour (161-160). The problem is that I don't think you can make a formula that can do math with the summary types. I was trying to do rollups in a different tab but it wasn't working. In excel I would do a SUMIF function where the criteria is that Month = Sep 2023 and Resource = X and have it sum total work hours and another one summing hours worked - holiday so I can subtract them.I'm trying to do this by resource by month. Does anyone know a

Hello,I have a formula field that is supposed to return a value ("updated") when a last-updated field is today.I live in another time zone, but all of my dates are set to Eastern time to reflect my working hours. Some of the posts I have read have said that a time zone error will produce this problem. To show that all time fields are set up for Eastern, I am including screenshots below. You can see that the last updated date/time is today (10/10), and the value of today is also 10/10.The formula field is not showing the value "updated." Here is the way the formula field is set up  Here is how the last-updated field is set upHere is the way the start date field is set up  I have also tried using "now" instead of "today."I am sure that there is an explanation, but I'm unable to find one based upon my searches of the community posts.Thank you for any help you can provide!