Formulas

HiIf anyone has any ideas about this I would be very grateful - I can't quite see a way through this one!One of the elements of the system I'm building schedules the use of specific tools. I would like to be automatically notified if a tool is double-scheduled, ie. if the duration of a booking clashes with another booking.As far as I'm aware, there isn't a way of defining timespans in terms of dates in Airtable, other than visually in a timeline or gantt view. The furthest I've got towards a solution is to work with start dates and durations, but I feel like this will get pretty complicated, and would need to include a complex set of IF() formulae that I can't quite fathom out.If anyone can see a clear way through, or just has any logical ways to progress, I would be very grateful.I'm also open to scripting solutions if that's the cleanest way forward.Jon

Question on the following formula.  I have tried just about anything and everything to get this formula field to only calculate the score based on after the date.  The formula works perfectly fine without using any date fields. Form type (single choice option), LH_GC_Def_1 (checkbox) field.   Current working formula for field GC#IF({Form Type}="QCLH",IF(LH_GC_Def_1=1,0,3))+IF({Form Type}="VOICE",IF(LH_GC_Def_1=1,0,2))+IF({Form Type}="QCLH",IF(LH_GC_Def_2=1,0,3))+IF({Form Type}="VOICE",IF(LH_GC_Def_2=1,0,2)) If I try to add in the date field to the GC# column (date of session field) using an operator, it looks like some portions are scored correctly and some are completely wrong, like it is only counting a portion. I have also tried to use IS_AFTER but the formula fails to work, or pulls up an ERROR!.  Any thoughts?

Hello community! We use airtable as an ATS (applicant tracking system) and now would like to extend it to project management.I have 2 different tables: candidates and jobs. I now want to create a new task table (classical kanban project management) where I can link to either a record in the candidate OR in the jobs table. As the "link to another record" only lets me refer to one other table, I am wondering if there is a way to link to a record in +2 tables.Any help is appreciated!Michael

is there a way of adding a joint array, concatenate to my primary field (containing Email and Full Name), but still be able to link to that table using email only. in other words, my contacts have different emails, and currently primary field is their email. but when i want to link them in other tables, it becomes difficult to find, as you dont always remember their email, so wanted to change primary field to also include full name, but then if i do that, my other automations that use the primary field to sync between tables won't work, as these were based off the email address only, and adding a name to it, changes the value obv. 

Hi everyone,I've been searching airtable forums for hours yet I can't find a solution that clearly explains what I need to do to achieve my desired results. There are different answers talking about using MIN() or something other formula, but I can't get it to work for me.I have a lookup field that pulls date entries from another table, but there are many duplicate entries I can't remove.So I used ARRAYJOIN(ARRAYUNIQUE(values), '\n') in the column beside it to rollup the fields and finally remove the duplicate entries. The problem is there are times attached to all entries as "T00:00:00.000Z" and all I want is the date part. For the life of me I can' figure out a way to get rid of them. Is there a way to achieve this result somehow? or to Format the dates?Any help explaining what I need to do exactly would be helpful, thank you.

We have a system for reviewing new tools where they are checked for meeting accessibility standards and security standards. These are two separate teams with different workflows. The integration team needs to know when a tool review is considered complete.I have two columns; one for the accessibility phase, one for security phase. I'm trying to make a formula for overall review status. There are multiple decision statuses for each. I would like to make the formula display either "Awaiting Decision" or "Review Complete". Review Complete would show if both columns have a value that indicates a decision was made.For example, for tool A, the accessibility status = Testing Not Required and security status = Complete, it should be marked Review Completed in the formula column.For tool B, the accessibility status = Completed and security status = Intake, it should be marked as "Awaiting Decision".I've tried a variety of IF(FIND( arrangements but I can't seem to get it right. I only saved the

How to calculate the counts' fields if it is newly created in existing records that we have a formula for the attendance sheet.

My table has a Rollup field (let's call it 'Titles Field').I used the ARRAYJOIN(values, ', '), so the titles are separated by commas.However, I want to add a Formula Field that show only the Newest/Latest title added to the rollup field.Please advise.

Hello, all. I am importing a CSV file into Airtable. Some of the CSV data ends up as Long Text fields in Airtable, and consists of blocks of text with multiple paragraphs. It comes in with <p> and </p> indicating these paragraphs.In Airtable, the <p> and </p> are preserved and the text is just one giant block with no breaks. What I would like to do is process these files to restore the line breaks as they're intended. I tried doing a search and replace to replace the HTML tapes with \n, but it didn't work. I'm not sure if I understand why, but I am guessing that \n is useful in the case of multiple fields being strung together with line breaks, not a single field that needs to have its line breaks preserved.If anyone has any ideas on how to accomplish what I need--preserving line breaks in fields brought in via CSV--I would appreciate it, Thanks!

Good Day,We added a CSV file with all our invoice amounts, and we have also added a CSV with all our credit notes.Is there a formula that will automatically deduct the credit note amount from the invoice amount based on the Document Type column?So I need a formula that will automatically deduct the credit note amount from the invoice amount based on the document type that they are.Screenshots are attached to show what it looks like when we import it. 

The problem is how to use the value of an entry in one table as a parameter for further calculations in other tables.Table "Settings"The Settings table has either (A) a separate record for each parameter, or (B) one record in which several parameters are stored (as separate columns). This is not essential, but the (B) option is better. I need one single select field with "EN", "ES", and "PL" values.Table "Translations"The following table contains translations into different languages: English, Spanish, and Polish of some fields. Let's call it Class, Item, Description. So here are the columns:EN_Class, EN_Item, EN_Description, ES_Class, ES_Item, ES_Description, PL_Class, PL_Item, PL_Description.Table "Base"And the 3rd table Base where the fields Class, Item, and Description contain values from Table "Translations" depending on which language is selected in the settings.Also, I think that storing languages in this way is not the best solution. Would be happy to get any help with this.Lin

Hi, I'm sure the answer to this is really simple and I'm just missing it, but could someone please take a look at this formula and let me know why some records would be throwing an ERROR, but others are working? I'm thinking I've got something in the wrong order maybe?We have a group events calendar set up and most events are one-off events, but we have a couple of recurring ones that I'd like to set up an Automation for to automatically generate the next event.This is the formula I'm using:DATEADD(DATETIME_FORMAT(SET_TIMEZONE({Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),1, 'weeks') Also, if I have a checkbox field that is ticked for 'Recurring Events,' how could I add an IF statement to check if that box is ticked first before generating the date, otherwise to leave the field blank? I've tried playing around with it, but I just keep getting errors, so it's time to ask all you amazing formula experts for some help.Thank you!

From the below URL, I am attempting to extract the number that comes after the pattern "page%5Bnumber%5D="https://api.leadfeeder.com/accounts/133259/leads?end_date=2023-01-01&page%5Bnumber%5D=39&page%5Bsize%5D=1&start_date=2023-01-01In this case, the result would be 39Some help on the formula would be greatly appreciated! Cheers,Brian

Hello everyone, I have currently the following formula in my table that shows "Yes" if the date in "Fin contrat" is in a future month. I am trying, but with no success, to adapt this formula to show "Yes" in another column if the same date is in the previous year (so currently 2022).Can someone please help me achieve this?Thanks! 🙂 IF( IS_AFTER( {Fin contrat}, DATEADD( DATEADD( DATETIME_PARSE( "01 " & DATETIME_FORMAT( TODAY(), "MM YYYY" ), "DD MM YYYY" ), "1", "months" ), "-1", "days" )),"Yes","No") 

Hello, I'm trying to create a Formula that will change Status according to the fields "Start Date" & "End Date". If Today > End Date = "Delayed"If Today < End Date = "In Progress"If Today < Start Date = "Planning"I have written the following formula that works for "Delayed", but shows "True" instead of "In Progress" or "Planning"IF(TODAY() > {End Date},"Delayed", AND(IF(TODAY() < {End Date},"In Progress"),AND(IF(TODAY() < {Start Date},"Planning")))) Anyway can identify my mistake? Thank you!!!

Hello,So currently, we do have a tick box on this view and manually tick it once it is the latest valuation.Do you have any ideas on how can I automate this? without doing the manual ticking?Another issue, I created this formula : IF({latestVal Date} = {Val Date},{Valuation Amount}," ") But it's giving me this outputI know that this could be done when you format it through currency but I'm having this error  "Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date." So what I've done is update the formula to  IF({latestVal Date} = {Val Date},{Valuation Amount},0) and it's giving me this result   The main goal of this formula is to not include those records that are blank or zeros. Is there any missing from the formula that I have set up? Thanks

Hello Airtable friends.I have a somewhat simple formulaic excel sheet that becomes very complicated when breaking it up to be used in Airtable.The goal:Based on the type of sale, each of 6 static 'sale stages' should have defaul percentage (out of 100) assigned to them. Then for each of those 6 stage percentages, I need to allocate all of them across a variable amount of Sales Team members. Here's an example:Sales Type (variable) = New LeadSales stages:1 = 10%2 = 30%3 = 5%4 = 25%5 = 15%6 = 15%Now for this sale, Person1, Person2 and Person3 all contributed. So for each stage I need to distribute their percentage (this is manual input)1 = 10% - Person1 = 3%, Person2 = 3%, Person3 =4%And so on through all of the stages.There's like 8 different sales types, of which the % distribution across 'sales stages' varies and the potential number of people involved in a sale can be up to 6. So to sum up variables:Sales Type % Allocation per Stage (based off of Sales Type)# Of Pe

New to Airtable... Trying to create formula that will calculate 6.25% tax but ONLY if the "State" column is "MA" (for Massachusetts)... I figured out the tax part (Price*0.0625), but not getting the If State=MA, only then calculate tax for the tax column (otherwise, if not MA, just want to see 0 in the tax column).Thanks!     

Hello,let us say that I have a simple table containg a list of products and the sale amount of each product. Now i want to add the share of sales of each product in this table.The only way I found to do that is to create a new table called "Total". This table contains only 1 line and is linked to the product table in order to rollup the value Sales to compute and store the Total of sales accross all products. This total is then brought back by lookup into the table Product, meaning the a new formula field (Sales / Total) can be set up.Problem is that this formula is not dynamic, meaning that if we filter the table, the share is not correct anymore since the total is not recomputed.is there a solution do set up a computation with a dynamic total responsive to the filters applied on the table?Thank youPascal

Hi, I have a field of clothing sizes which varies for each garment record. Ideally I'd like to be able to set up a single select field with options that vary depending clothing sizes held in the multiselect or linked field for that particular record.I can display the options so the user manually picks one of those out of all the sizing options, but it would be nice if the options presented only pertained to the specific garment.Is there a way to do this? Any thoughts would be much appreciated!

I am struggling to get the DATETIME_FORMAT to return the correct time for me. For example, my Start Date is 10 January 2023 00:00 and in my formula column, I just want it to extract the time of the "Start Date". So I put in this formula: DATETIME_FORMAT({Start Date},'HH:mm')However, it keeps returning the time as 16:00.Can anyone help? 

Hello Community! I need some guidance... I have a few columns with fields that are Single Select and one of the options is "COMPLETED". I am looking for a way to create a formula for it to add "how many COMPLETED fields" I have for that record... For example if it was Excel I would try an "IF" formula or "COUNT" to add how many columns have the COMPLETED field, but here I just can't figure it out. Anyone know how to go about? 

I have two fieldsa start day for a task (date field)an estimated number of workdays the work will take to complete (a number).I want to add those workdays to the start date and count ahead to the date the work will be completed. I do not want to count weekends.I've tried the formula below to estimate the date the task will be completed by combining my two fields. DATEADD({Imaging Start Date},{Forecast Workdays for this Batch},'days') However, that counts weekend days as workdays, thus returning an earlier forecasted completion date.For example, if the work starts 6/1/23 and should take 10 workdays, the returned date should be 6/14/23, not 6/10/23.Is there a way to account for this in my formula or the data? 

Hello!I have a table of clients, each of whom has been assigned a coach.I have a second table of appointments which is coming from Acuity. In that table, I have the clients linked so I can see all of the client's meetings with anyone in our company.What I want to do is find the FIRST time that a client has met with their coach. This would not be their first meeting ever; that would be too easy.Within the "clients" table, I have the "client" name, the "coach" name (and a ton of other data)Within the "appointments" table, I have the  "client" name, the "coach" name, the "calendar" the appointment was booked on (which would match the "coach"), and obviously the dates and times of appointments. I can use "min" formula to find the first appointment, but I'm struggling with the first appointment with the specific team member that is the "coach". Thank you! 

I am trying to create a tracker for trip expenses. I have a list of Attendees in one table, then Receipts in another with a "Charge To" field (since sometimes expenses wont be shared evenly across the group) that is a link to the Attendees table. What I would like to do is auto-calculate from the "Charge to" field on the Attendees table in a "Amount Owed" field.So basically I am looking for a way to look at "Charge To" see if the Attendee line is there, if so, get the the length of the number of attendees and divide by it and add it to the amount. I can add some columns on the Receipts table if I need to for the amount per person but the thing I cant figure out is how to check the multi select if the Attendee line is in it. Any suggestions?---- Tables Set up ----Attendees:Name (Single Line Text) | Total Owed (?) Receipts:Date (date) | Total (currency) | Charge To (multi select [Attendees::Name])