Formulas

Hello everyone,I have the following problem, which I haven't been able to solve for weeks. Basically, I want to do a Count with a dynamic condition on a date, which is not possible in the Count field settings.I have three linked tables:1 table A "Contracts" with insurance contracts. It has an anniversary date field.1 table B "Vehicles" linked to table A for vehicles attached to insurance contracts. It includes two fields with a date of entry into the fleet, and a date of exit from the fleet.1 table C "Annual periods" is linked to table A contracts. It contains the annual entry and exit periods created from the contract anniversary date.What I want to do is obtain from table B "Vehicles" to table C "Annual periods" a count of the number of vehicles:1 - on the entry date,2 - on the period exit date.The calculation of the count

Dear AT Community!I'm collecting arrival and departure dates for an event setup period from workers. They can register through a form. In my base now I have two date fields with an arrival and a departure date for each worker. The setup period spans from 5th May to 16th May.The question is: How many workers are on site each day?In order to answer that question I created 8 formula fields and wrote a simple formula to extract the needed information from the date fields. The output looks like this:Now in the bottom row the sums are given which equal the number of workers on site each day.I tried to create a bar chart in an interface showing the days on the vertical axis and the number of workers on the horizontal axis. To no avail. Any ideas on how to approach this would be appreciated. I'm kinda stuck.Best regardsHenry

Hi,I'm using AirTable to track potential sales opportunities and would like to visualise on a chart the revenue per month.  This is similar to the Gantt chart view but instead would show the size of revenue rather than simply a project.For example, if an opportunity lands on the 15th April that will last 2 months, completing on the 15th June and worth £100k, this would be spread over those months.  I.e. £50k in April, £100k in May, and £50k in June.  Then a bar chart would total all these by month.In Excel this is relatively easy - you create a column for each month that calculates how much of the revenue in each month (can just about do that here but it's not easy).   Then you create a bar chart for the table and it shows it easily.  Can't find a way to do that in AirTable or with an app. Any and all help is much appreciated!!Dan 

Hello,The following formula doesn't work, with the field {Days (from Boosts)} being a lookup field representing the number 30. Is there an issue with using lookup fields within the DATEADD formula? DATETIME_FORMAT(SET_TIMEZONE(DATEADD({Created},{Days (from Boosts)},'days'),{Tutor Timezone (from Tutor)}),'DD MMM YYYY') The formula above works if I replace {Days (from Boosts)} with the number 30 however. I've just done some more testing and the following formula works as intended:DATEADD({Created},{Days (from Boosts) form

Hi everybody,if I can ask for help with formulas in IF statementsI have developed Glide app with Airtable, works really good.I have a table WorkOrders, and among other columns, I have:"Start time", "Start kilometers", "End Time", "End kilometers", "Total km", "Total time", "Status".This formula is in column "Status", works good:IF(OR({NameSurname}="",{Startkm}=""),"CREATED",IF({Endkm}="","IN PROCESS",IF({Endkm}>{Startkm},"COMPLETED")))How can I add another Status, Canceled?Button CANCEL in Glide updates the fields: Startkm and Endkm to 0 (zero), StartTime and EndTime to CurrentTime (same data in StartTime and EndTime)Can you help me with the formula?Thank you very much    

I have two tables Experiments and Readings.Each experiment is linked to two or more reading records. Each reading has a DateTime field, and some readings such as pH and temperature. The pH and temperature readings are number fields.I want to use the first and last sets of readings in the Experiments table in some numeric formulas.In Experiments table I use a rollup field with MIN(values) to find the earliest DateTime in the linked readings. In the Readings table I use a lookup field and copy the {First DateTime} reading back into the readings table. So that all the Readings for a specific Experiment contain a field which is set to {First DateTime}.Then still in Readings, I have a field {First Temperature} with the formula:IF(DateTime={First DateTime},Temperature, '') Then back in Experiments I have a corresponding field {First Temperature Reading} that looks up the result in the {First Temperature}

Hi, I am doing a form for a remiders request, and I need that the filed "fecha de envio" the person is requesting the reminder have not the option to select a day before Today (current day).

Hola necesito generar in ID de solicitud para mi form que sea facil de buscar o indentificar

Hello Airtable community,I am working on a project where I need to automate some financial reports in Airtable and I would like to ask for your help to solve a specific challenge related to conditional sums in several tables.Context:I have a base with several key tables:[REPORTS] - Commercial: Records monthly orders per year (2022, 2023, 2024) assigned to different commercials.SUMMARY: Contains a record for each month of the year and I need to add up the order totals for each commercial, divided by year.Requirement:I want the "SUMMARY" table to have three columns (2022, 2023, 2024), each showing the sum of sales for a selected salesperson in a single selection field in the same table. The challenge is to have these totals update dynamically based on the selected salesperson.Attempts:I have tried using rollup fields, but these do not allow me to filter dynamically based on another selection in the same "SUMMARY" table. I am co

How do I use sum values automatically calculated when grouping fields for other formulas?I have to calculate the sum of values of a grouped field in other formulas. Is it possible? someone told about rollup, but I did not manage to obtain any useful result. I would need to obtain something like that in the screenshot in the sum field...Thanks in advance for your help 🙂 

Hello, I think I need a help of Airtable community even though there is built in AI helper in the formula field. Here is what I am trying to do and have no success for some unknown for me reason. I'm creating an automation that with the click of the button will send an sms text message to my customer. I've recently experienced a problem with Twillio campaign approval (they are giving a hard time), but I've got an approval with TextMagic. So, Text Magic accepts sms as an email, then converts it to text and then sends it to my customer. I have a dedicated Airtable field for the phone number. Based on this, I've created another formula field that adds "@textmagic.com" after the phone number, the formula looks like this: CONCATENATE(Phone,'@textmagic.com')Everything looked good, so I started with my automation, but my automation failed at the end due the format in the formul

Hi All I would like to know if someone can assist me with create the formula used in the time off id column for this video at the timestamp 2:04 https://www.youtube.com/watch?v=Q-MEmIHCHU4&t=8s

Hello - I have a date column of when an event will occur (Showcase Date) & I am wanting a formula to populate deadlines based on number of days before or after this date. Here is the formula I created but it is not working. Any suggestions? I have tried: DATETIME_FORMAT(DATEADD({Showcase Date},{Number of days},'days),'MM/DD/YY')

I have a column of text I need to convert in to title case for an import into another system.Is there a way to make this title case with Regex?There is things like:"PREPARE FOR PRINT""BLUE/GREEN""WHITE / BLACK"Anyone know if this is possible?

I have a simple task tracker with a single table containing a list of tasks.  I have a self-linking field to allow for a single level of subtasks.  I primarily use Interface lists to view tasks.  However, I also assign tasks to be completed by varying people.  So when I filter to a list of my assigned tasks, the nesting feature breaks.  Airtable will only show me tasks if they are assigned to me.  So if I am assigned to the parent task, I don't see the child tasks.  And vice versa.I thought it could be helpful to append an icon to the front of subtask names to cue me that something else is depending on me completing that task (haha) but can't figure out how to write a formula for that!In the data views, the parent task has the child tasks linked in the subtasks field.  So the child tasks have empty subtask fields.  Therefore, I have nothing to search for.Anyone have thoughts or worked through something similar? 

Hi All! I am hoping this is an easy problem to solve 🙂I thought this would be really simple, but I'm struggling.I have a project "Start Date" field. Every time one of my users updates that "Start Date" I would like AT to automatically retain the last start date before that change in a "Last Start Date" field.So if "Start Date" was 01/05/2024 and someone changed it to 12/05/2024, then "Start Date" would read 12/05/2024 and "Last Start Date" would read 01/05/2024. Each field would then update every time the "Start Date" field is consequently updated, which can happen numerous times.Any help / advice would be very much appreciated. I am hoping that this can be done with AT as opposed to using any external connector.......

I have a grid view with a field that shows volunteers that have volunteered along with the date they volunteeredI want to figure out how many volunteers volunteered each month - how do I find how many unique volunteer names appear in this list, and if I filter for the specific month, will it still work?Thank you! 

Hello, wondering if someone could help me correct the formula in the photo.  I am hoping for this to happen:If OLD JOB# is blank, display current 2 digit year (CREATED DATE) & "-" & 4000 + ID#. If OLD JOB# is NOT blank, display OLD JOB#For example, looking at the last 2 rows at the bottom, 42 is correct, but 43 should display "24-4183"

Hiya all! So, I am trying to create a formula that uses each of the checkboxes in my table as a way to track payments that have come in. I need each checkbox to equal/represent to the amount listed in the {Payment Amounts} column and for the number of payments that have come in (ie - the number of checkboxes checked) to be represented by a progress bar where the final tuition amount is listed in the table too as {Tuition}.  I have the below pasted formula so far, but its obviously not working: IF( COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th},

Morning! This is probable really simple...but I'm struggling to find the formula i need and wondered if you lovely people could assist please?Basically I'm trying to add a date in a formula output, so if the ccf column has a number in it copies the number across to the formula output column, and if the ccf is empty then it says 'no ccf 10/04/24'  (todays date)This is as far as i've got - i cant figure out how to copy the info across or add todays date on - anyone know the simple fix for this please?   IF({CCF  *Customer order number*} >0,  "CCF ATTACHED", "NO CCF") 

HELP!! I just can't get the following to work into one formula....  basically I have four sets of dates that I need to set status' for to trigger email reminders (1st Pupdate, 2nd Pupdate, 3rd Pupdate, 4th Pupdate). I need a single formula that joins them all together using IF OR or is it IF AND???? I just cant get it to work!!IF(  {1st Pupdate},IF(IS_AFTER(TODAY(), {1st Pupdate}), "🔴 1st Overdue", IF(IS_SAME(TODAY(), {1st Pupdate}), "📆 Due Today", IF(IS_AFTER(TODAY(), DATEADD({1st Pupdate}, -1, 'week')), "⚠️ Due Soon","👌 OK")))) IF(  {2nd Pupdate},  IF(IS_AFTER(TODAY(), {2nd Pupdate}), "🔴 2nd Overdue", IF(IS_SAME(TODAY(), {2nd Pupdate}), "📆 2nd Due Today", IF(IS_AFTER(TODAY(), DATEADD({2nd Pupdate}, -1, 'week')), "⚠️ 2nd Due Soon","👌 OK")))) IF(  {3rd Pupdate},  IF(IS_AFTER(TODAY(), {3rd Pupdat

Hello,I need help with my formula.If <total amount paid> is not blank, then the cell becomes <ACT Planner>. However if <total amount paid> is blank, then cell becomes <EST Planner>.I got this far, but can't get the 2nd part to work:IF({Total Amount Paid},{ACT Planner}) Thank you!

Hi all, first-time poster. I'm working with form submissions right now, and I'd like to check if the free text of a Location form field matches any value in an existing table's list of locations. For instance, if we received 'Bayview South' in the 'Location' form field, then I would want to check whether 'Bayview South' appears on the below list:  ABLOCATIONSUMBayview North1Bayview South1Sea Breeze1

Hello Everyone,I am having a hard time with a specific formula. I want to set a formula that will calculate a certain percentage based on what driver is selected in the driver field. Example: for the Dispatch % field i need a formula that will go like this. If driver is Dima set Dispatch % to be {Total invoiced} *.13 if Driver is Alex set dispatch % to be {Total invoiced} *.07 Thank you for the help! 

is it there is a way, preferably with formula/s, to perform element-wise multiplication of two arrays of numbers?For example, if I have two fields with arrays [1, 2, 3] and [4, 5, 6] respectively, can I write a formula that produces [4, 10, 18]?_I would also need a formula for:Column1 (array 1): 2, 3, 5Column2 (array 2): 5, 4, 1Expected output: 7, 7, 6is this achievable?