Formulas

Hello, I have posted about 15 questions this board and I have always received a solution my problem. So thank you all very much. I think I have a really tough one this time. I have a base to manage memberships and donations and legacy gifts for a not for profit. There are 6 membership levels with specific dollar amounts attached to them. So all is well as long as a person pays an amount that corresponds to an existing membership level. But when they pay an amount that is not a preexisting level, I have to assign the difference to a donation.I have a Currency field that records the actual amount paid. I have a formula field (thank you Alexey_Gusev) that shows the membership level closest to the payment amount. For instance, $35 would show as an Individual membershi which is set at $25. I need to then add the remaining $10 the donation field. The next level is $45 for a Family level. To be completely honest, I have no idea where to even

Hi there;I'm very new both to Airtable and formulae, so I hope this is just a beginner's issue and is not a function or possibility currently beyond the possibilities Airtable provides.I have a formula field (Sim Required?) that I would like to populate a green checkmark from a combination of possible options in the multiple select field (TikTok / Instagram / Router).The multiple select field has 4 options:TikTok 1, TikTok 2, Instagram and Router.I would like the green checkmark to appear under these conditions:TikTok 1 only is selected.TikTok 2 only is selected andBoth TikTok 1 and TikTok 2 are selected.I've managed to have the checkmark appear only when TikTok 1 is selected; when I try with only TikTok 2 or a combination of 1 and 2, the green checkmark doesn't appear.Thanks for any help you provide, 

Hi,I have a table to have a list of equipments and when they were cleaned. Apart from equipment details I have these fields:Last maint date (a date field - to be manually entered)Next maint date (formula which returns a date 120 days after Last maint date)Actual cleaned date (date field - date on which an equipment was actually cleaned - to be manually entered)Status (Formula - I want this to be evaluated for 3 conditions and return text phrases accordingly)Conditions:If the 'Actual cleaned date' is greater than 'Next maint date' then return 'Delayed' in StatusIf the 'Actual cleaned date' is equal to 'Next maint date', then return 'Cleaned on time'If the 'Actual cleaned date' is lesser than 'Next maint date', then return 'Cleaner early'  I tried this nested if IF({Actual date cleaned}>{Next m

It seems the more I learn here the more ideas I come up with that I need help with.I have a membership payment table. There are 6 levels of membership.Individual  $25.00Family  $45.00Patron  $60.00Sustaining  $100.00Benefactor $250.00Sponsor  $1,000.00I would like to write a formula in the membership level field that fills in the membership level in the membership level field using the amount entered in the amount of the membership payment field. There are 6 membership levels as above. So if they pay $35, then it would put in as an Individual membership, etc. The $10 difference would but put in a field of the donations table as a donation. I will start that formula when I get this working.This is what I came up with but it has an error. Am I missing an OR statement maybe?Many thanks, Scott IF(AND({Amount Paid}

Hello, I'm trying to find a function that will pull all of the long text; rich field checked boxes away from any unchecked boxes. I've found the solution linked below and it worked great for the opposite problem. Does anyone have a formula to solve pulling only checked boxes? https://community.airtable.com/t5/automations/extract-filter-unchecked-items-from-a-rich-text-field/td-p/27036Subtasks are all our tasks. Next steps are unchecked tasks. Activity completed should be only checked boxes from the subtasks table.  Thank you!!  @TheTimeSavingCo @Brian_LWA&nbsp

IF({Last Renewal},DATETIME_DIFF(TODAY(),{Last Renewal},'days') > 365,"🔴","🟢") The above formula, without the first {Last Renewal}, works fine. But I have records that are empty and I want them blank. I obviously am not constructing my syntax correctly. I want to say that if {Last Renewal} is not blank, then do the following. as I said it works if take out the first {Last Renewal}. I'm sure it is simple. I just don't see it. Thanks, Scott

Hi,Would like to set up a Trigger Reminder with the formula;IF(AND({Reminder},NOW() >= {Reminder}),"Trigger Reminder")But instead of NOW, would like to customized it to xx hours from created TIME, specifically. Have only seen customize formulas for a certain date or number of days but nothing for a certain time period. Any help would be greatly appreciated.

Hello! This filter does not take into account the time part when applied in URL: filterGreaterOrEqual_Date=2023-10-17T22%3A00%3A00.000ZExcept for when I set it at 22:00 where it filters OUT all records with date October 17, 2023 and "allows" them back in when I set it to 21:59.Other settings such as 06:00:00.000Z or 18:00:00.000Z show every record of October 17. Dates on my table are:17 October 2023 00:00 UTC17 October 2023 00:00 UTC17 October 2023 10:00 UTC17 October 2023 10:30 UTC17 October 2023 22:30 UTCIf I remove the time altogether it catches Dates very well: filterGreaterOrEqual_Date=2023-10-17 works fine. Any Ideas please? 

I am trying to auto-number a group of records for one person in a table with multiple people listed with numbers 1,2,3, etc.. In Excel, I would use a formula to add one number to the above cell, and it would number all rows sequentially. I usually manually number each record, but I am hoping a simple formula would number the list.Here is a screenshot of my table consisting of the records I need based on a filter.  The row with # in the title is where I would put the formula.  Any suggestions? 

My field has a name followed by a number - SAM ADAMS (1234).   I need to put the 1234 in it's own field.   What is the best way to write a formula to do that?   

I would like to know how to auto number based on the building link and location name in my base. as per the picture below. Can someone let me know if this is possible and if so the solution.TIA. James. 

Hi everyone, i'm a new user of airtable and just wanted to ask if there's a way to do this?The first Col row 1 (red box). If that specific row is not blank.It will fill out the entire next col (blue box).  

I'm trying to write a formula to check whether a lookup field contains a specific value.These lookup fields might contain one or multiple values, e.g.applesapples, pearspears, bananas, strawberriesI've been using the formula IF(FIND("apples", {fruitName}), "yes", "no") In this case, I would expect 1 and 2 to return as "yes", but only 1 is returning yes. Find seems to require an exact match, and if there are other values in the lookup field, it returns false. Does anyone know what I'm doing wrong here?

Hello and thank you for considering to help me!Every day, my Airtable is supplied with additional lines through an x ​​bias.How can I create a view that only collects last week's rows?I only manage to get the lines from 7 days ago and not all those from last week.Any idea ? 🙂Kindly

Hi community.I have a single-select field called Sector in a table called Projects. I'm trying to cast this field to a string.The reason I'm trying to do this is that I'm trying to build an automation, in which I will need a conditional step that will check this stringified single-select field to a multi-select field in another table to see if the multi-select field contains the the selected value in the single-select field.Thus far, I've created a formula field in Projects with various formulas (or formulae, for the sticklers out there), including:- ARRAYJOIN(Sector)-Sector-LEFT(Sector)No matter what, when I go to the automation it won't let me choose the field because AT says "Cannot assign list of string to a string".Please help!!!Thank you!

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

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

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 cr

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