Formulas

I have no idea where to start on a formula for this. :stuck_out_tongue: What I would like to accomplish is for Airtable to compare two start times and two end times and tell me which of them matches up. One start/end time would be for an event or concert, and the second would be for employee availability. I want to know if an employee has a start/end time that overlaps an events start/end time. If the two times match I would like Airtable to tell me which employees are available during that event or concert. I have two tables, one table is for the event and its information then other table is for keeping track of employees available time. Table 1: is for events and projects. It has a column that has a start time and end time. Table 2: is for employee availability. It also has a column for start time and end time as well. What formula should I start building out. Should I combine the tables together to make it easier? Any suggestions

Hello!I'm trying to create a field to display the month name. My start column is EU dates like that : 10/6/2023Precision, sometines the number of the month is unique "6, 7, 8" but from october, it switch to a double number "10, 11 or 12".Any idea ? 🙂

Hi guys! Thanks in advance for your support.Right now I have several tables in a database, one of the tables contains the events that occur, and another table contains the types of events that exist (in order to count them). The question is, how can I count only the events that have occurred in a specific month or have been involved in that month? For example, if I want to know the number of events that have occurred in May, but an event started in April and ended in June, it is not going to count if I use a conditional which searchs for event started or ended in May.Do you have any ideas of how to implement it? 

This is driving me nuts. I have a long text field (rich text turned off) that seems to have multiple line breaks. Formula I used was Substitute(Field,“\n”,“”) and while it seems to work it also doesn’t. I tried with putting in a value instead of “” so I could see it working and it is working but also airtable seems to be keeping paragraphs anyway. Any help would be greatly appreciated.

I want a percentage bar where if desktop, tablet and mobile is ticked then it will show full 100% filled percentage bar Here’s my table link: Airtable

Hello dear AirTabler, we are working in Influencer Marketing with AirTable and want to create a form that allows Creators to automatically upload their content.In the AirTable we map the complete campaign handling, so here are already the names of the influencers.The form should be neutral, i.e. no linked field can be created so that the creators can select their name and then upload content.How can we make it so that the creators enter their @handle in the form and the content is then automatically entered into the existing column of their name?

Dear Airtable Hive Mind,I have an Actors table and a Movies table. Each Movies record refers to the Actors table via multiple Link fields, i.e. Movies.Actor1, Movies.Actor2, Movies.Actor3,..., Movies.Actor10 in order to capture the full cast, each of which is a Link to the Actors.Name field.Q1 Each Actor is a member of a particular Acting Association (Actor.ActingAssociation field). How can I pull the Acting Association for each Actor ideally into the Movies table, i.e. Movies.Actor1Assoc, Movies.Actor2Assoc etc. Or do I need a new table, and if so, how to do it there?Q2 How can I gather a distinct (unique) list of Actors into a new Movies.AllActors field that lists each actor noted across that Movies record (10 Cast Member Actor fields as listed above), ideally as objects rather than text, that will automatically update when a Movie.ActorX field is edited. Plus a related column called Movies.AllAssociations showing the distinct list of Associations for the

Hello guys, i have a table where i calculate some values which can also have a Zero.That means that i sometimes get 3/0 which leads to a Nan error.I already tried a couple of formulas but i do not get it. So my question right now is how can i put the cells which would have Nan as a value to 0? I would be happy for a solution. Thank you.

Hello you!I'm looking to remove 2 characters of fields, from the right of the cell. I have a list of prices, like that, no currency :1570197401549I'm looking to get this :157 Є97 Є15 ЄThe deleted numbers are the cents 🙂Cheers! 

HelloI've managed to create a formula that adds x number of days to a start date and pushes the result to a work day if the end date is Saturday or Sunday:IF(AND({Direction start date},{Number of days},"days"),WORKDAY({Direction start date},{Number of days}),"days"),{Number of days})The result seems to work fine, however the formating isn't great as the answer is is like this 2023-06-02T00:00:00.000Z .  I've clicked the format option but that doesn't let me do anything.  Any suggestions would be gratefully received! 

I have a table that has numerous columns of date fields that tells me when a person first attended a specific workshop. (this is a linked field and reads from the individual workshop tables)I want to be able to have a column that shows the earliest date of all the dates that this person(record) attended - essentially to give me the date they started engaging in workshops with us.Does anyone how if this is possible and if so what formula to use(I tried a MIN formula but it just gave me numbers rather than the date and I couldn't see an option to format it as a date)

Is there a way to automatically add a date and time stamp when I type something in the notes? For example [date] [time] called xyz and left message… [date][time] emailed abc about xyz…

Why do I get a total of 2 days when it's clearly only a 1 day difference?    

HiI have in my AirTable a list of Names. Lets say I want to find all results with last name "brown". I am making a search for people with formular like this:filterByFormula: LOWER({TABLE_ID})="brown"How can I do it that if I search "bro", or even "own", it will also send the result of someone with name "brown". Meaning I want to receive when the String contains the seach word, not just when its an exact match.Thanks

Hey everyone, I need some help with a simple formula I can't figure out.    "/Volumes/" & SUBSTITUTE( {Dept. Server Folder}, "\\", "/" )    I am trying to pull the content of another field on the same card. I want to add the plain text /Volumes/ to the front, then substitute instances of "\\" with a "/". Our team has macs and PCs, and I am trying to solve a bit of a headache that our content managers feel.

HiI added the extension: Search to the air table,This link: https://airtable.com/marketplace/blknmXKkZjB7scdSq/searchThis is a great search function to search all the fields in the table. Is this available in an api format, not just on the web interface of the AirTable? Thanks

Hello,I have a standard rate sheet where I share different charges to my clients. Sometimes, some clients have better rates than others, thus I have to have one rate sheet for each client in excel files.I want to create a single general rate sheet and create a rules table, so that I can add/subtract a specific amout or a percentage to a specific client. After that, I would need a kind of dynamic rate sheet where I can see the resulting rates for a specific client.The formula that I am thinking about is: Base rate + extra value + extra percentage.The last 2 values should be extracted from the rules table for the specific client, This is the structure of my database:TABLE 1: Ratesheet Columns: Name, Rate, Min, Max, Tags, Notes, Rules (link)TABLE 2: Clients Columns: Name, Notes, Rules (link)TABLE 3: Rules Columns: Name, Add value, Add percentage, Clients (link), Ratesheet (link)

HiI have 8 fields with numerical values and I want to create a "summary" in a new field, which would show per each record which of those 8 are the 3 top ranked fields (name_field&value). Is that possible?Thanks   

Hello Airtable Community,I have 2 tables: Attendance and Performances. People will fill out my ticket request form, submitting their preferred number of tickets for Performance Dates 1,2,3 etc.  This gets recorded in the Attendance table under their school name and quantity goes under "Number of Tickets." I also have a Performances tab that tracks total number of tickets available for the performance. I want to use a rollup or formula to connect the two tables and deduct the total SUM of tickets claimed from a Total Tickets Available number that I set (right now it is 1,000 tickets.)Ideally I am able to make this appear in both tables: Attendance and Performances. Can you help me?   

Hello, I currently have this question needing to solve "If a branch's average monthly gross revenue in the last quarter is higher than $1500, its revenue split rate will be 80%, or else it will be 70%"I want the result to show Percentage 80% and 70% as result because after that I will use them to calculate the net revenue. ASAP. Thank you.

Hello! I am trying to extract text from within Parentheses and can't get the Regex formula quite right. The data comes in like this:Highrise Intro (First Last)I want to extra First Last and eliminate the rest. Thanks

Hi! I have looked pretty extensively in other questions and feel like I just must be missing something. We have a diaper bank and have one field that is a Multiple Select. That field has many selection options; we don’t care about counting most of the individual items in that field but when either Pads, Tampons, or Pregnancy test is selected, I’d like the value of “1” to be entered into the Pads, Tampons, or Preg test field (so we can correctly count how many of each item we are giving out.) Can someone please help me with the formula? Thank you!

I have start dates and end dates for each project in a list of about 50 projects. I want to create a filter that will only show me records that were active between the start date and end date during specific fiscal years (where I set the FY date parameters). Some projects span multiple fiscal years, so "on or after" or "on or before" doesn't cover everything.

This is my data, inclusive of images, and the ensuing results I'm obtaining from Airtable on Make.com are clearly visible.However, there seems to be a slight issue. Instead of receiving the value 1014.40 as expected, I'm getting 1014.4000000000001.Could you guide me on how to rectify this discrepancy?      

Hello!I am trying to create a formula which gives an output of difference between two dates (Premium date & Last month from Today date - as seen in the last 2 columns). The base is primarily used to manage my Insurance payments, so keeping that context in mind if its helpful. The issue is - I have two Premium dates in my table (because some times Premium might be half-yearly frequency payment). So, in order to get the difference in dates for only 1 date (Yearly premium) - I used the following formula:IF({Plan status}="Active", DATETIME_DIFF(DATETIME_FORMAT({Premium pmt date},'DD-MMM'),DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'DD-MMM'),'DAYS'),'NA')And this is working fine.But, when I introduce 2 dates into the formula - I also need to use a MIN() function that gives me the lower of the difference val