Formulas

I have two numerical values, A and B, that I am using to create a range field. Here is my current formula:CONCATENATE({A}, "-", {B}). So far so good. The tricky part is that not all of the entries have a B value, in which cases the result will look like {A}-. Ideally I would like it to just return the single value from A without the hyphen in this scenario. I have tried using an IF to say IF(BLANK({B})) but here is where I get lost.  Any advice is appreciated! I am very new to formulas... 

Hello everyone, In Germany, there is a regulation in the Occupational Health and Safety Act that states workers who work on Sunday or on holidays receive an additional allowance on their salary. This involves a complex formula, which even ChatGPT struggles with. Maybe there is someone here who is smarter than AI.My table includes a date field including time field named {Start} and a date field including time field named {End}. I want:1. Formula field = formula that calculates the hours/minutes that fall within the period of Sunday.Example : Max starts his shift at saturday 8 PM and works until Sunday 4 AM. The formula should now calculate 4 hours.2. Formula field = formula that calculates the hours/minutes that fall within the period of holidays. The holiday dates should be defined in the forumla itselfs = ( 01.01.2024 / 29.03.2024 / 01.04.2024 / 01.05.2024 / 09.05.2024 / 20.05.2024 / 03.10.2024 / 25.12.2024 / 26.12.2024)Example : Max s

Hi! I created an Airtable to help plan my wedding. I have a table that's for Guests and in this table I have fields for their first and last name and their title (Mr., Mrs., etc.). I created a field that adds all of these fields to create their formal name on the invitation envelope. Right now the formula reads: Title & " " & {First Name} & " " & {Last Name} & " " & "&" & " " & {Guest Name (from Relationship)}  Here's where the problem comes in, for my single guests it puts the "&" after their name, for example, "Mr. John Smith &". How can I fix it so that it adds the "&" for the guests that live in the same household but doesn't add the "&" for the single guests? I was thinking an IF() statement but I'm not sure how to work it for that. I've also attached a picture of how it looks currently in my

I am trying to generate a formula where:1. If date in field x is greater than 1953, then 1 is put in field zOR2. If checkbox is checked in field y, then 1 is put in field zI came up with this formulaIF(OR({NO DIGITAL} = 0), ({CODE-DATE} > 1953), "1")However, this is producing a statement that reads 'true' if {CODE-DATE} > 1953, and '1' if {NO DIGITAL} = 0 (that is to say, box in {NO DIGITAL} is checked)Any idea why this is happening or how to get the results I want?

Hello! I have a formula to count how many workdays are left between a data and “today” and when the reference data is “after today”, the output returns a negative number (as it should). I am looking for a way to get my formula to only return “0” when that situation occurs. I haven’t figured out the nested if statement logic to make it work. Does anyone have a solution to this? Thank you!

Hi! I am trying to summarize a range of fields from one record. I know that there is a formula Sum({Column 1}+{Column 2}+{Column 3}…). However, I am looking for a formula to choose all the columns between Column A → Column X. I will have a database with many columns and I need to summarize multiple records quickly. Is there any formula that can help? Or what are the possible solutions for this? This is an example ! Thanks for any help!

I’m currently working on a setup where I need to reference data from the previous record in my Airtable base. Specifically, I want to pull data from fields in the previous entry to use in calculations for the current record.Here’s what I’m trying to achieve:I have a field called Current Tank Level in each record.I want to automatically populate a field called Previous Tank Level in the current record with the value from Current Tank Level of the previous record.I’ve tried setting up linked records and using rollup fields, but I'm facing challenges with automating the process to ensure the Previous Tank Level field always reflects the correct value from the previous record. How am I able to grab the previous record entry "Tank Level value" and automatically add it to my "Previous Tank Level" record?

Hi all,Wondering if there's a filter or formula solution (not sorting) to filter all results down to only the "Soonest one" ranked according to date field. I have a form in one table linked to a view in another table, ideally so people can only sign up for one linked choice (it's a monthly event registration). Currently I have the filter set for Date within the next month which mostly works, but a day or two before the event, both the current and next month's start displaying (because it's only 28 days away, etc.) Perhaps my logic brain isn't on today, but I can't think of good filter terms to only include the soonest event. The other thought I had was a formula which would assign numbers or filters to the nearest date (-1 for past, 1 for soonest, 2 for next, etc.) and then I could make that the operative filter, but I don't see an obvious example of that in the Formula Reference Base or guidance. Any advice? 

Hi all, I was wondering if you could help me create a formula that would output an alphanumeric ID based on variables in other cells in the row. I'd like to create an ID that include:First, if Cell A = xYx then add "Z" to start. If Cell A = YxY then add "ZD" to start.Then, take first letter from name and surname, and then add these. So John Smith becomes JSThen, add first 4 digits from date field (DOB). So, 14/11/1980 becomes 1411So, as an example, ZDJS1411.Thanks so much community!

My Rollup field has (correctly) returned this array to me ARRAYJOIN(values) by looking at linked records (4 People) and their dietary requirements (dietary preferences are determined by a multiple select field):None,Allergy to Nuts (other than Peanuts),Gluten-free / No beef / Allergy to Nuts (other than Peanuts),NoneAnother formula field formats this array into an easier to read arrangement:1 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)1 x NoneWhat I would really like is to find a way to combine any duplicated items and show the correct count for them, such as:2 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)Any advice on how to do this please? I want any formula to work dynamically with the content of the array, as this will make it easier to update the multiple select options without needing to rewrite any formulas that contain verbatim references to e

Hello, community! I need to create a formula (or find some other way) to keep track of changes to individual cells in "column A" via a second column that auto-populates with first entries in column A but does not change if column A changes after that. So for example:  Column A = Project Start DateWhere a user manually enters the date they begin a project.Column B = Original Project Start DateWhere it auto-populates with all existing dates in Column A + new dates added to empty cellsBut does not update itself when a user manually changes the date in Column A after that. The goal is to be able to allow users to adjust or move out their start dates as needed but also keep a record of what the original date was without adding any other manual steps. I really hope this makes sense! TYIA for any help or ideas! 

I have a linked table called "Voucher Codes" and as you can see from the attached screenshot, I can click the + and select a voucher code from this linked table and assign it to a record in the Order Fulfillment table. This is great, it works, however it shows everything in the view "Available" in the "Voucher Codes" table.  Is there a way I can have it ONLY show voucher codes in the "Available" view if it matches the "Course" field in the "Order Fulfillment" table as the attached screenshot shows? For example, if the Course says ITIL 4 Foundation w/TAKE2, it should only show voucher codes that are titled ITIL 4 Foundation w/TAKE2.  And if it the Course says ITIL 4 Foundation, it should only show voucher codes that are titled ITIL 4 Foundation, and so on. Is this possible?

I have various contracts and various contractor invoices that I need to review, and I want two formula fields to confirm whether the billing periods covered by each invoice - and the invoiced amount - are "valid". My table is pulling in the contract start and end dates, as well as the budget balance and total invoiced, from another table. Right now, I have 3 manual entry fields: 1 for Billing Period Start date, 1 for Billing Period End date, and 1 for Invoiced Amount. I created a formula field to confirm whether the billing period falls between the contract start and end date, but it currently shows billing periods that start or end on one of those dates as invalid. IF({Billing Period End}<{PoP End},IF({Billing Period Start}>{PoP Start},"Valid","Outside PoP"),"Outside PoP")I thought I could adjust the < to <=  and so forth, but figured there must be a more elegant way to do this. I also want a field that will confi

I'm building a base to help track my company's product lineup and I've built an Interface for each one of us 3 founders have our own To-Do interface, populated from a To-Do table. This To-Do table gathers information from other tables such as customer's name, product selection and other items. I want a column "Extended Task" to populate with a short checklist automatically, based on a selection of a "Task Group" field (which I've made a Single Select drop-down field in an effort to keep the names consistent). I thought of using an IF formula, where IF "Task Group" is "Quality Check", then the "Extended Task" field would get populated with "Verify Unit Powers On. Verify IR Remote Works. Visual Examination. Inputs Tested.  Doors Open Smoothly. Lights Operational." in checklist form. But not only can I not figure out how to do this complicated IFTTT formula, but I am also not sure if this is the best way to go about it.Here's an example:In this example, I've copy and pasted

Hi all, So I have created a base here (it is a gallery view)https://airtable.com/appPv62y3frc8BYJA/shrVTQGn7420FaGvUEverything in the base works as it should. The single select dropdown works great, but in the embed view, the single select dropdown doesnt exist. As in, there is no dropdown feature. Any ideas?   

How do I convert a duration into seconds?I would like a formula for the "Seconds" column that automatically calculates this. Understand this is probably so basic, but would appreciate any help. Thank you!

Hi all,wondering if you could help with the below, I need to give 1-5 rating to certain value ranges. Its daily revenue and you get rated based on your daily performance.This is the formula: =IF(AND(K2>=0,K2<=30),"1",IF(AND(K2>30,K2<=80),"2",IF(AND(K2>80,K2<=120),"3",IF(AND(K2>120,K2<=200),"4",IF(AND(K2>200),"5",0)))))K2 in Airtable is: TotalI cant get it to work, so any suggestions would be greatly appriciated.Thank you!

Hello Airtable Community,My table includes an event title field as well as one for an event date.Event Title:The Chemical Regulations LandscapeEvent Date:June 6, 2004Formula:LEFT({Event Title}, 16)&" - "&{Date}Formula Result:The Chemical Reg - 6/6/2024All good so far…but there are also records that have an event title and no event date, so the formula above  returns #ERROR! for these records.So, for the records sans event date, how can I modify the formula so that it returns the first 16 characters of the event name instead of generating an error?Many thanks in advance for your assistance!John

Hello!I want to use the results of an extension into a formula to automate a field.For context, we are using a SendGrid extension to send out emails and once those emails have been sent I want to be able to automate a status field to change from 'email to be sent' to 'email sent' - any idea on how I could do so? I'm also hoping this means that it would keep the emails that have failed to send as the status 'email to be sent' so we can repush them.TIA!

Hi,I am using AT for the CRM purposes. I've got a list of proposals we're working on within my Proposals Table: https://airtable.com/appGL773TiIYzLwZD/shrzetl6rN5gnxAYzNow, what I'd love to calculate is: based on the stage selected (and % probability assigned to it) and proposal value, I'd like to calculate probability value.So if stage = "Proposal + SOW submitted (50%)" then, it's 50% of the value.Do I need to create another table for the probability? Have my stages outlined, then % and create a formula within my 'proposals' to calculate it?Thank you!

If I use this formula:IF({Whole-Body Conditions}!=BLANK(), {Whole-Body Conditions}, "") & IF({Body: Left Foot}!=BLANK(),CONCATENATE(", ", {Body: Left Foot}), "")I get this result when {Body: Left Foot} is NOT empty, WHICH IS WHAT I WANT: But when {Body: Left Foot} is EMPTY, I get this result that has an extra COMMA:When I take out the comma in the second IF statement, then there's no commas at all whether or not {Body: Left Foot} is empty.So... what's up with that? Shouldn't it ONLY put the comma IF there's content in {Body: Left Foot}? 

Hello - I need to calculate a date, in certain circumstances, that is 75 days out from another date in my table. I’m using this formula:SWITCH({S/N}, 'no', DATEADD(DOB, 75, 'days'), 'NOW',DATEADD(DOB, 75, 'days'),'NA')which calculates correctly, but includes a time stamp in the results (2024-07-24T00:00:00.000Z). What do I need to do to my formula to get the date only, in mm-dd-yyyy format? My apologies if this is covered somewhere else. I’m not finding exactly what I need in the guides or past posts to this forum.Thanks,Susan

Hi,I need help creating a formula for scoring a quiz that I am making in Airtable. The questions are a combination of single select and multiple select. There are 100 questions total and each question is worth 1 point if answered correctly. The multi-select questions must be answered exactly right to get 1 point, otherwise they get 0. I am running into a problem with the multiple select questions in the formula. With the way I have it written, it will count 1 point if all three of the correct selections are selected in any order. BUT if one of the incorrect selections are selected in addition to the three correct selections, it is still counting 1 point. How can I tell it that if any one of the incorrect selections are selected that the score should be 0? In this particular example A, B, & C are correct. D and E are incorrect and should not be selected for them to get the point. The highlighted area in the attached formula is the part of the formula for the multi select question. T

I have a field that has a ton of linked records and I'm trying to format that into a nice list for use in the Pages extension.  It's generally coming through with commas and quotation marks separating it.  I found this formula in another thread, which seems to remove the opening quote marks, but not subsequent. IF(LEFT({Marketing Tactic - Detail}, 1) = '"',SUBSTITUTE(MID({Marketing Tactic - Detail}, 2, LEN({Marketing Tactic - Detail}) - 2),'""','"'),{Marketing Tactic - Detail}) I'm trying to format something like this:"Digital Signage (BOH) - TVs installed to reach branch salespeople in lunch rooms, warehouses and other strategic locations. ", Email to Salespeople - Email to increase brand or product awareness with salespeople usually offering a stocking incentive. , Flyer for Salespeople - Flyer used for vendor to increase awareness about products or stocking offers available to salespeople., Post on Intranet - Announcement posted on the intranet website

Hi!Two formula questions - I have (4) different date fields with timestamps and need to identify the latest date across the four fields. When doing a Max value, it does not bring in the timestamp or makes it 12:00a as a standard. I also have (2) date fields that I need to calculate the datediff in hours and leaving the field blank if the second date field is empty instead of the NaN.Please help! Thank you 🙂