Formulas

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?  TIA!

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}, {8th}, {9th}, {10th})=0 , 0 , VALUE(SUBSTITUTE(SUBSTITUTE({Tuition (from Course Tracker)}, "$", ""), ",", "")) / ({Payment Amounts} * COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th}))) The issue thats coming up with this formula is that when there are no check boxes ticked, the formula seems to be working and the % paid shows 0%, but then when I tick a check box, it com

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 Pupdate}), "🔴 3rd Overdue", IF(IS_SAME(TODAY(), {3rd Pupdate}), "📆 3rd

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 Breeze1Palms North1 In Excel, I would normally just use an XLOOKUP and IF formula on the above table with the below syntax: IF(XLOOKUP("Bayview South",A:A,B:B,"0")>1,"In list","Not in list")In this case, the XLOOKUP formula would check all of column A for 'Bayview South,' returning a 1 if 'Bayview South' exists and 0 if it doesn't. The IF formula then returns an 'In list' value if 'Bayview South' is anywhere in the list or a 'Not in list' value if 'Bayview South' is not in the list. The reason I'm looking for this solution is to run exception automations if a value&

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?

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: VALUE(SUBSTITUTE(SUBSTITUTE({Tuition (from KCIT Tracker)}, "$", ""), ",", "")) / ({Payment Amounts} * COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th})) I have pasted a screenshot of the table for reference - is there anyone that can help me figure out this formula? x 

Hello,I've been using a very hacky method of generating a Markdown table using a formula in my base. The below example uses Markdown in quotes for the table structure, and then pulls from field data to populate the table.Example: " # DIVINATION " & "\n" & " | Divination | Result | Description | " & "\n" & " |-|-|-| " & "\n" & " | " & " Soothsayer's Deck " & " | " & {Soothsayer's Deck} & " | " & {Soothsayer's Deck Description} & " | " & "\n" & " | " & " Constellation " & " | " & {Constellation} & " | " & {Constellation Description} & " | " & "\n" & " | " & " Position " & " | " & {Position} & " | " & {Position Description} & " | " & "\n" & " | " & " Moon Phase " & " | " & {Moon Phase} & " | " I then use an automation to populate a "Calculation Output" field using an example from @kuovonne in this thread.The result outputs:  | Div

I'm trying to calculate cost/price in my shop by hourly rate. I have the Time Tracker linked with my Duration Field, but something is off in the calculation. IE one duration is 0:43 (0 full hours;43 minutes) but the calculation cost is showing $3,870. It should be $64.4999 with the formula (Duration/60)*90. How do I get it to calculate the correct amount wether the billable hours are 1h:43m or 0h:43m? Thank you in advance

I have two rollup field I need to evaluate and for some reason I can't get it to work.Field1: {rollupStockMinimum}Field2: {rollupNonStockMinimum}Field1 is empty, but Field2 is not.I only want the formular to do something when there are something in both fields.I've tried with this very simple formular  IF(AND(Field1, Field2), True, False)   However for some reason it evaluates to 'True' despite Field1 being emptyI've tried to just do IF(Field1, "True", "False") where it evaluates to False.What is it I'm missing or don't understand?

I'm trying to have the formula with a dateadd calculation be blank of a certain field is blank. However if I put the  DateFormat function around the whole If formula it will either result in an error if the input is Blank or if any option could be blank it will not allow the Formula Formatting as a Date field either way. How can AT recognize a formula field as a date field if the date could be blank based on a conditional formula or related blank field???  DATETIME_FORMAT(IF({Weekday Planner}=BLANK(),BLANK(),DATEADD(TODAY(),(({Auto Date 2}-(WEEKDAY(TODAY())))*24)+15,"hours")),'MM/DD/YYYY h:hm A') 

Good morning,I don't understand why the function is refused, even though the content is basic. Do you have an idea?Thanks

Okay guys, got a doozie... Basically, in the screenshot I have a table, which is the main table that 99% of things sprout off from. We have customers, who through a webflow form are able to select accessories to add on to their purchase. That gets relayed to the table in the form of a checkbox being selected/unselected. Here's the problem -- I need to calculate accessories inventory, for each individual column AND be able to automatically update the rolling amount in another table, which uses the accessory name as the primary record. I've tried EVERYTHING I can think of from linked records, to roll up fields, to automations, and formulas. If anyone can calculate how this can be done I'll literally send you a $100.

I want to combine cells into one address block, but if there are no addresses I want the cell to remain blank. I'm having trouble creating this. Right now I'm getting a random common in the box: This is the formula I'm using: CONCATENATE({Address 1} & "\n" & IF({Address 2}, {Address 2} & "\n") & {Address 3},", ",{State / County}," ",{Zip / Postal Code})

Hi there,How can I enter hours for one tasks and days for others in the same project?Thank you!

Hi there,For the same task, how can you define a Daily Time Required of 100% on certain days or periods, and a lower percentage on others?Thank you!

I generate records through the week that all pertain to an event on Saturday. For organising these records into a week-by-week historical view (and for ease of human use on Saturday), I want to be able to automatically tag all of these records for the Saturday they pertain to. I have searched and tried a few things - this is the formula I got closest with:IF(WEEKDAY(Created)=6,DATEADD(Created,0,"days"), DATEADD(Created,6+WEEKDAY({Created}),"days")) Where "Created" is the date the record is generated. However, this generates a record for the previous Saturday. I don't really understand the formula I've cobbled together from old threads well enough to understand why. Does anyone have any advice to get it to fill that date as the following saturday from the "date created"?

I have a formula setup to show a status based on an 'Expiry Date'.This works from a date column {Expiry Date} with a column that works out the difference in days called {Diff}, this uses the formula: DATETIME_DIFF({Expiry Date}, NOW(), "days") Then is the status column I am using: IF(Diff <= 0, "❌", IF(AND(Diff > 0, Diff <= 28), "⚠️")) This works perfectly, my question is how do I add an additional "✅" status for all records that are in date? What would I need to add to the status column formula?