Formulas

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 🙂

Hello.I am trying to find records where a rollup results is less than or equal to a function. Both are formatted as numbers, and both work when I search for records in each column independently. Is there a solution for this?

I have a somewhat complicated formula I wish to create, and as a formula n00b, I don't know where to start. Here's the problem I'm trying to solve:1. I have a series of tags I enter into a single text field, where each tag is separated by a comma. I have two conditions to meet before I list a product with the tags, otherwise, the Make.com automation won't work, and I'll have to spend a fair amount of time deleting a partially created listing and re-editing the record in Airtable. These conditions are 1) I can't have more than 13 CSV tags in the field, and 2) I can't have any duplicate tags.2) I would like to create a formula that displays an ❌ in the formula field if the tags CSV field has more than 13 tags AND/OR duplicate tags. If both conditions are met, display a ✅.Thanks in advance to all the formula wizards out there willing to help! 

Hi all--We are a publishing company and we sometimes publish the same content multiple times throughout the year. We have a field set up "Publish Date" that shows all of the dates. The problem is when I try to have these feed into our general publishing calendar view, I can either only choose between the Minimum date (the first date) or the Maximum date (the last one), I can't figure out how to show both dates on the calendar (without having it span across the entire time in between the two dates). Is there possibly a way to separate the dates into separate entries and enter them into the calendar that way? Thanks in advance!

So Im pretty new to Airtable and not sure if I am going about this the right way and wonder if someone can point me in the right direction if not. Basically I am trying to use a multi select option to calculate the Extra costs involved in a job with solar installations which I have a field named Extras. So lets just say I have an option of installing on a tile roof which costs an extra 50 which I have added using the formulas below in a separate field called Extras Total.  IF(FIND('Tile Roof', Extras),50) +IF(FIND('3 phase', Extras),200) +IF(FIND('Solar Hot Water Removal', Extras),800) +IF(FIND('Optimiser', Extras),100) +IF(FIND('Panel Removal', Extras),20)  Where I am having trouble is with panel removal and optimisers there may be more than 1 panel or optimiser so is there a way that I can add multiple amounts to these particular options. My only thought would be to add multi options for each one e.g 1 panel removal, 2 panel removal, 3 panel removal etc. But this way w

I am trying to add an AND statement to this formula:IF(     {Date Received} = "",     "Unsure of due date",     IF(        DATETIME_FORMAT({Date Received}, 'MM/DD') = DATETIME_FORMAT(DATEADD(NOW(), -14, 'day'), 'MM/DD'),        "TRUE",        "No match"   ))The AND statement I want to be if a cell does not equal a certain value from a dropdown. So I think on its own, the and statement should be something like:{Status} != "MoveOn"I just can't figure out where to put each piece and how to close the parentheses. The screenshot is my table without the AND part of the formula.Any help would be greatly appreciated!

Hello AT Community, I am looking for a formula that capitalises the first letter of every word before the " |"  in a string. 3 examples belowEXAMPLE 1The output for "ARCHITECT | Tom Jerry” to be ArchitectEXAMPLE 2The output for "BUILDING AUTHORITY OFFICER | Road Runner" to be Building Authority OfficeEXAMPLE 3The output for "CIVIL ENGINEER: Energy Performance Certificate | Bugs Bunny", to be Civil Engineer: Energy Performance Certificate Thanks in advance   

Hi, everyone! I need help in combining names from two Rollup fields with the result below:First Name                        Last name                        Formula Field (Result I want)Mary                                  A.                                       Mary AMary & John                     A & A                                Mary & John AMary & John                    A & B                             &n

Problem formula: DATETIME_DIFF({Task Due}, TODAY(), 'hours') Short Version: It's currently 5pm Pacific. The Task Due is due tonight at 12:00am Pacific. DATETIME_DIFF reflects -24. Why is this hours calculation reflecting 24 hours instead of 7? Long Version:I have a field, DEADLINE DAY CALCULATION, that I want to reflect TODAY if Task Due is today, TOMORROW if Task Due is tomorrow, DAY OF WEEK if Task Due is after Tomorrow, and PAST DUE if Task Due is before today. Because Airtable functions in GMT and our team is in Pacific Time, using 'days' would change at 5pm our time to reflect that TODAY is PAST DUE. I cannot fix this by toggling on Time Zones in the Task Due field because we do not use times for due dates, and this toggle only works if times are not hidden. Last week I built a formula to offset the GMT change by 8 hours:IF({Deadline Day Calculation} <= "-17", "PAST DUE", IF({Deadline Day Calculatio

I created a rollup field for my leadership score column but it only is showing the sum of one of the projects (project A) and not the other (project C).

Hello everyone, In Germany, there is a regulation in the Occupational Health and Safety Act that states workers who work between 11 PM and 6 AM 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 a formula that calculates the hours/minutes that fall within the period from 11 PM to 6 AM.Example: Max starts his shift at 8 PM and works until 4 AM. The formula should now calculate 5 hours.Can someone help me?

Hello All, I need help with a nested IF statement that references a formula date field. I'm not sure if this is causing my issues, but I can only get my formula to output two of the four values. In the formula below, "Expired" and "Missing" appear, but "Expired" is not accurate when I update the date.What I am trying to accomplish is:- If the date is past today, display 'Expired'.- If it is within six months of the due date, display 'Expiring'.- If it is greater than six months, display 'Active'.- If no date value is in the field, display 'Missing'.If anyone can let me know what I am doing wrong, I would appreciate it. IF( {H.I.P.S - Due Date} < TODAY(), 'Expired', IF( AND( {H.I.P.S - Due Date} >= TODAY(), {H.I.P.S - Due Date} <= DATEADD(TODAY(), 6, 'months') ), 'Expiring', IF( {H.I.P.S - Due Date} > DATEADD(TODAY(), 6, 'months'), 'Active', 'Missing' ) ) ) 

Hi all,Using Make, Airtable and an API, I built an automation that scrapes reviews of a cultural event from Google Maps. On Google, the date on which a user left a review is not given. Instead, Google provides us with a time period that matches the amount of time that elapsed since the user left his/her review (e.g. 'one year ago'). This type of data does not make it easy to sort reviews in a chronological order. Consequently, I went about transforming this type of data into dates.For this data conversion, I created intermediary fields – Time Unit Count, Time Unit Type and Search Date – that I then combine in a formula in the Date field. But I'm unsatisfied with 2 of those intermediary fields' formulas.1. Time Unit Type field: for this field, I used a series of nested IF statements because I didn't manage to use the SWITCH function properly in this use case. This gives me a long and not-very-clear formula.Would any of you guys have a more elegant solution to suggest?2. Time Unit Count

I have a table with a list of contracts that has fields for CompanyName (link record to a company table that lists all the companies) and ContractStartDate.How do I filter or find the oldest record for each Company based on ContractStartDate? A company can have 1 or multiple contracts.Thanks!

Hi all!I am currently working on a base in which our staff members will be able to fill out their weekly timesheets. I live in Canada and here the standard hours of work is set to 8 hours in a day and 40 hours in a week.In our company, the standard hours of work for a week is set to 35 hours. Any worked time that goes between 70 and 80 at the end of each Pay Period (2 weeks) is put in a bank. I can't figure out how to properly sum those hours without creating an astronomical amount of fields so I need your help.In my base (which is in French) I have 6 tables :Feuilles de temps (=Timesheets) which is linked to Pay Periods, People, Days and ProjectsPériodes de paie (=Pay Periods) which is linked to Timesheets, People, and WeeksPersonnes (=People) which is linked to Timesheets and Pay PeriodsSemaines (=Weeks) which is linked to Pay Periods and DaysJours de la semaine (=Days) which is linked to Timesheets and WeeksMissions (=Projects) which is linked

Hi,I need some help with an issue I'm facing. I have a formula field called "Order Quantities" and a "Meal Quantities" field (Long Text) that references it. I'm using the Fillout form to gather customer information. However, the quantity field in Fillout is not connected to my Airtable.To address this, I created an automation to pull the quantity details into Airtable (see the attached file).I'm looking for a workaround to sum all the quantities of each meal for all records in the "Order Quantities" field without changing the structure of my table, as I need to launch this system this week.Thank you!Here is the formula I'm using for the Order Quantities: CONCATENATE(  "Lamb Keema: ", IF(    ISERROR(SEARCH("Lamb Keema x ", {Meal Quantities})), 0,    VALUE(MID(      {Meal Quantities},      SEARCH("Lamb Keema x 

Hi! I'm trying to create a formula for the following columns:1: First Name2: Last Name3: Pen Namewhere If "Pen Name" is blank, use "First Name + Last Name". I keep getting stuck somewhere and getting invalid formula errors. 

Is there a way to see/track who has made edits to a formula or field? This feature is very helpful for viewing individual records, but it'd be even better if I could view who is changing formulas.