Formulas

Hi, So I’m using PHP which has this functionality built in (basically I give it a number and it converts it to how I would like it…) specifically I’m in Australia but the format I’ve chosen to go with is like this: +61415932797 so let’s break this down +61 = country code 415932797 < number obviuosly In Australia though when you call someone on mobile you dial 04… so it would be like 0415932797 if that makes sense (since you don’t use country codes when dialling within the country)… so I’ve got a bunch of these mobiles like this in my AirTable: 0402 930 888 - like this which I want to convert to +61402930888 - as you can see no spaces (random spacing in my original) - ideally also skip any fields that have text in them but that’s an easy thing… so… yeh long story short how do I regex 04 01 933 833 to +61401933833 also keep in mind a number could all ready be country code… but then aga

Hi. I’m looking for a way to calculate an average time of day for an average start time and average end time. Airtable has the date and time field, and the time duration field, butI need the average start time and end time, as separate values.I need to accommodate for a start time being before midnight or after midnight.This is to get an average time a person fell asleep (potentially before 11:59 pm or after 12:00 am) and their average wake up time. Based on my pretty thorough knowledge of Airtable, I can’t figure out a way to do this because Airtable’s fields don’t offer you a time of day without a specific date.A more traditional business purpose of this calculation would be calculating a person’s average clock in time and clock out time, as separate values, but still accommodating for before or after midnight, which could be applicable for a business with

i have a sync to jira.. i want to assign an airtable user based on the jira user fieldFor example: if  CM2 field contains John Smith, Mary Jones or Kate Day.. Can i create a formula that assigns John Smith, Mary Jones or Kate Day as an airtable user vs just a text box.. so that they get a notification that work has been assigned.  or does this need to be an automation with a user table?Thank you

I am building out a process to make billing time and invoicing easier.  Unfortunately the formula I'm using either results in $1 or a total that's completely incorrect. How in the world is it calculating $216? Should be 7 * $33.33=$233.31  

Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.DATETIME_DIFF({Sell}, {Buy Date} 'days')Well, there may be a problem, but I can't see it... can anyone help with the formula to display the number of days between two dates 🤔 🤔? Thank you.Here's what I'm getting for these 3 fields:Buy Date, Sell, #Days1/12/2020, (No End Date), NaN7/10/2023, 1/31/2024, 177120001/1/2020, (No End Date), NaN9/19/2023, 1/31/2024, 11577600

I would like to extract the months from dates in a multiselect field. If I use Month(Months) I get NaN, If I use DATETIME_FORMAT(Months, 'MMMM') I get #ERROR!.  Anyone know how to do this?  Thanks, Scott

Hi - desperately need help in figuring out what in the world is not working. I am creating a production timeline where the campaign launch is the starting point. From there, I want to count and report certain milestone dates bases on a typical timeline. These milestones are working days (M-F) so not a normal "count backwards". I have to do this for multiple types of campaigns, so instead of making stagnant formulas I'm referencing dates on another table. yes, I looked in to automations and record templates, but they don't allow for relative dates so this is the workaround. Example) Campaign 1Milestone 1: Day -13Milestone 2: Day -10Milestone 3: Day -7Milestone 4: Day -2Milestone 5: Day -1Campaign Launch: Day 0Example) Campaign 2Milestone 1: Day -17Milestone 2: Day -15Milestone 3: Day -12Milestone 4: Day -5

Hi there,we try to find solutions for this use-case:We want to create "logic" URL's for our data-sets, what we can do at the moment is creating a formula "www.airtable.com/base-id/table-id/" & recordID().What we would like to define is the part recordID(), because all of our fields have a type-number (for example AA550). So it should be a formula "www.airtable.com/base-id/table-id/" & type-number with the result:www.airtable.com/base-id/table-id/AA550The reason for that is, that in the past often dataset were be deleted by someone or we have to transfer the database. We're using this URL's in an other database and if the recordID is changing, we have to check that again (yes

I'm using airtable to manage all my seasonal employee hiring. I have one column where from a multiple selection I choose their job title (eg. Camp Ops, Kitchen, or Waterfront). I want another column that show what certification are required based on the job title I selected. I believe this should be a IF or SWITCH formula.Currently I have a working formula that is IF(Job='Camp Ops',"First Aid & CPR" ) But I can't figure out how to get the formula to also be IF(Job='Kitchen',"First Aid & CPR) and IF(Job='Waterfront',"Lifegaurding") and so on. 

Hiya! I spent a lot of time googling and trying to find the answer but not having any luck. I need to find a formula that displays the previous Monday date based on a date in another field. Would also love to know what each element does so I can learn a little too rather than straight copy/paste. Thank you so much!

Hello! We were previously using this formula to calculate a recurring date in the future: DATEADD("Publish Date", 7, "days")  However, we're now getting an #Error Message in our base.  I did some searching and found this blog from Airtable.  I tried using their exact formula in that blog as: DATEADD({Publish Date},7,‘days’) but it is telling me "Sorry there was a problem saving this field. Invalid formula. Please check your formula tex

Can someone assist me with writing this formula? I'd like to basically have a column in which the staff or faculty nominee column is combined with the value. For example: Cameron, Ali - Innovative SpiritI'm running into an issue were if the staff column is not populated the staff one is, and I need the names to pull from either or both- I tried using and "if" statement but am not having any success. Additionally, if there are multiple names linked, I'd like to separate them by a comma. Screenshot attached for reference of the table!

Hi there - I am looking to create a column that will essentially lookup the field values of columns Q1 - Q4 and output the latest number.We input quarterly projections and want to be able to identify the most recent number by looking at 1 field ("latest projections"). *Note - not all fields will have data from Q1 - Q4.I tried using the IF function but that only seems to work with 2 different fields max. ( IF(Q2> " ", Q2, Q1) )If I have data entered in Q1 field and then in Q3 enter in new data in the respective Q3 field, I want the "Latest projections" column to display that Q3 field since it its the most up to date #.I hope this makes sense. I will attach a screenshot for reference where I manually updated the "Latest Projection" field. 

Is there any substitute for INDIRECT in AirTable?

I have a table that generates specific tasks for clients on a frequency that required an interesting set up, but got me the result I was looking for (leveraging multiple formulas, jump tables, and linked records). Currently, the tasks are generated into a table called 'Client Work' that my team works the tasks out of. I have other team members that need to submit tasks to that same table, but are one-off tasks, not auto generated tasks. Unfortunately - the primary field (Task Title) is a formula generated field that combines multiple look up fields to generate the title, so I am not able to have Task Titles. I am looking to add an "OR" function, IF the fields that the formula is referencing is blank (aka: a work around to have another field that acts as the task title that will only be used whenever a task is generated via a form). For the task name issue – I feel like I have a potential solution, but need some guidance on the formula for th

How to make a line break when I do strings concatenation? For example, I have 4 fields and I do a concatenation: "field_1: " & value_1 & ", field_2: " & value_2 & field_with_lots_of_text I’m trying to do smth like that: If I do like thins, it works ok: But I want to add more data and add a line break:

Can I add a table to “long text” field?I want to add table that include columns " code - description - price" example:code - description - price“1167 - bike tire - 75”

Hello,After the customer submits a request form to the client for order fulfilment, if the client has a follow-up question for the customer - Is there a feasibility/workaround to submit a comment/question on the request so that it can be re-directed to the customer for further clarifications/information? Can somebody suggest a workaround? Thanks!

I have a field where people can select multiple options. The question is “what are some emotions you experienced” and they can select from 9 options. I want to know how many people choose Joy and Anticipation, whether they’re the only options selected or if they’re part of a bunch of responses. I’ve found several ways to set up a formula field that tells me if someone ONLY chose Joy or Anticipation, but it doesn’t pick up if someone chooses Anticipation and Sadness or Joy and Anticipation together. There are too many possible combinations to realistically set up a nested IF formula. Is there a way to write a formula that will tell me if someone chooses either option, regardless of whether they chose other options too? I have tried OR, FIND, SEARCH, and REGEX, though it’s likely I’m just setting it up incorrectly! If it helps, the data is coming in through an external site via Zapier, so if needed I can change the field type from multi-select to

Hi Everyone, I am trying to prefill a form with a range of record information and just can't seem to get it to work. I would like to produce a unique prefilled form link for each record and fill the invoice number in for each.   My current formula:CONCATENATE({Base Form Link},'?prefill_Invoice=',{Record ID}) But when I click the concatenated form link it is putting the record number in the invoice field (which is supposed to be just a 5 digit number) I tried moving the invoice number from the primary field (a number) to a single line text field but still wont work Any help would be mu

It appears that "tel:" hyperlinks does not work to create a button that opens a URL to call a phone. The button is not clickable.However, the "mailto:" URL shortcut works, but not "tel:"Are there any workarounds to this that will put a clickable link in any view type? (grid or interface?) 

Hithis should be simple but i am stuckAAABBWhats the formula for a rollup in a linked table to have "3A, 2B" as a result? thx!:M:    

Hola a todos! Estoy trabajando en un proyecto en donde los usuarios podrán escribir artículos. La idea principal, es que las keywords se generen automáticamente a partir del texto del artículo. He buscado por todas partes pero aun no eh logrado esa funcionalidad, como tampoco logré hacer que una vez que esten detalladas las keywords pueda hacer un conteo de las mismas para ver si alguna se repite. Aguardo su ayuda si son tan amables. Gracias

BonjourTout à fait débutante dans airtable, après avoir parcouru de nombreux forums, je me permets de venir vers vous pour trouver une solution à ma formule ou une autre idée. j'espère répondre aux critères et codes du forum.Dans une table « Badges » il y a 2 champs pertinents l'heure de début et de fin de journée de travail, un 3 ème champ pour le temps de pause à soustaire du temps de travail et le 4 ème champ , appelé « Total_heures » qui calcule la différence entre les relevés d'heures de début et de fin de jour moins la pause.

I've been reading through several posts about similar topics, but I'm just missing something here. I imagine some of this is due to some problematic base design, as I've been building it as we go but here's where I'm at - I have a table of club members. We are in the yearly renewal time, so, since I didn't have a way to allow submissions from our membership form to update existing records, I have redirected submissions to a new table. As it is possible folks have different emails, phones, etc, and they aren't aware of the formulated member ID I've assigned them in our main table, this seemed the easiest way to collect new and updated form submissions. So, my plan was to do a comparison between are All Members table and the 2024 Membership table - 1. In All Members table, If email exists in 2024 Membership table, update a field to indicate member has renewed2. In All Members table, if a record in 2024 Membership has the same email, compare ot