Formulas

Hello everyone, I'm trying to figure out a formula to use when a selection option is made add the modified time. I know that there is a last modified for when fields are edited by a user. But what I want is when a certain option in a select field is selected to add the time. For example user selects, "New Board" from the select field - then the formula would automate the time when the option was selected "4/12/23 4:05pm". Thank you!

Hi all! I am building out something to help with vaccine planning (yay!) and need something that can essentially help me do the following: I have a single select field called “Vaccine” with Moderna (First Dose), Pfizer (First Dose), Moderna (Second Dose), Pfizer (Second Dose), and J&J as options I also have a date field (“Clinic Date”) indicating when the vaccine clinic takes place for each of them, and how many doses were administered. What I am looking to do is to automatically calculate a field for the second dose clinic (i.e. it would need to identify the type of vaccine and calculate the next date based on that. For example, First Dose Pfizer - calculate for me the next date as 3 weeks out. For Moderna, the next clinic needs to be 4 weeks out. I should add that I did try creating the following formula, but Airtable does not seem to like it: SWITCH( {Vaccine}, ‘Pfizer (First Dose)’, DATEADD (3, ‘week’), ‘Modern

Hi,I am trying to use a formula for reach of our vendors on how many times a request has been sent to them by file number. I have them group by state so it show all the file numbers that went to that state. What I need it for vendor x to show that 70 request have been sent out because file number xwz have been order.ExampleVendor X is in AZ, TX, GA and FLVendor A is in TX, GAVendor B is in FL We have file 01 in GA, 02 in TX, 03 in GA, 04 in AZ, 05 in TX, 06 in FL So Vendor X would have 6 orders, A would have 4 orders and B would have 1 order

I have customers purchase information. in Make.com I'm able to store all of the customer's purchase information into a Long Text Field that looks like such:37466774995108, 37466777616548, 43650268266737, 37466778697892, 37466778632356, 43650344222961, 43650236907761, 37466778173604All of these values are linked to another record that has the Product information. How do I convert the numbers into Linked records. I can do this manually, but I want to automate this. I know you may be asking, but I have over 122 different products, so I would rather not manually add them. Also I'm using Make.com, and the only way to automate this is to upload as a comma array. So I need some sort of formula to convert a comma array into a linked field. 

Hello!Pulled up a list of three dates from a linked table.  In this table, there is a separate date on each record.  Looking for a way to find the latest of the three dates, that is not later than the separate date in the record.  I can find the last date using MAX, but am struggling to integrate the IF statement of not surpassing the stand alone date on the record.  

I want to map specific job roles to job categories. "Open Roles" in Table 1 has the specific roles, and Table 2 has the mapping between the categories and the roles. If "Open Roles" has a matching option in "Old Role", then I want to add the "New Role" value to the "Open Role Categories" field in Table 1.Worth noting that "Open Roles" can have multiple options and may need multiple mappings.

I am setting up an automation by concatenating 7 fields. These Fields are automatically populated as the user enters their info into a web form.Formula:CONCATENATE(Report_Link1,{Borough No},Report_Link2,{Project_StreetAddress},Report_Link3,{Proj_Street_Name},Report_Link4) The output I get is: https://a810-bisweb.nyc.gov/bisweb/PropertyProfileOvervie

I’ve read several posts similar to this already, I but haven’t figured it out. I’m trying to count how many times the word “Retouch” appears in this Rollup record.

I have two tables within the same Base.Table A is a list of yarns and has a column Length which is the total length (integer) of yarn available for the yarn named in that row.Table B is a list of patterns and has a column Length of Pattern which is the total length of yarn required for that pattern (integer).What I am trying to do is take the individual Length and compare it to all of the Length of Pattern entries and then return a Concatenated list that shows all of the patterns Name of Pattern that are <= Length.I've tried creating a linked field, with a lookup that shows Length of Pattern, but I can't work how to make it automatically search across all of the possible entries and return the result I need.Is this actually possible (not the end of the world if not)? 

I really suck at this so looking for support from the community.In theory - i would like to be able to input a URL into a field, and in another field return the URL's preview thumbnail image (type attachment) ideally through a formula or script. 1) Any ideas how to do this through a formula?2) If doing it through a script, i'm even more a noob. What would be the script?

I am using Make.com to pull data from Airtable.I want to filter the records based on three Formulas. I've checked them individually and they seem to work, however, I do not know how to combine them into one filter.These are the individual formulas:{Image 1 URL}!=BLANK(){Date Uploaded}=BLANK(){Date Downloaded String}="{{formatDate(now; "YYYY-MM-DD")}}" What is the right way to combine these 3 formulas into one?

Our staff are supposed to submit their timesheets for the previous day before 8:00AM. Some of them are not turning them in on time, and I would like to have a formula result that shows "Late Timesheet" when the sheet is submit TODAY() after 8:00AM. I have a formula set to just show the Submission Hour (eg. Created = 4/6/2023 7:07AM; Submission Hour = 7). I have tried several variations of the following:IF(AND({Created}=TODAY(), {Submission Hour}>7),     "Late Timesheet",     "")No matter the condition of the timesheet, the result is always blank. Thanks in advance!

I am unsure what formula to use.I have a field in AT where you select the promo type. I have a view where I group things by promo type... my problem is there are instances when no promo type is selected as it does not fall into any promo type. For records where there is no promo type selected I want the word "Evergreen" to populate so that it still falls into a grouping name instead of saying "Empty"I was thinking it might be IF({Promo Type} =BLANK(),"Evergreen") but it does not appear to be working.. can anyone help me with the proper formula?

Hi there Airtable Community! I work at an indie movie theater, and we use airtable to keep track of print traffic as well as enter showtimes. I'm a complete beginner here, and would like to find a way to generate base showtimes for each theater screen a couple of months out. As of now, I'm individually duplicates posts and editing them and it's soul killing since we have two locations, 5 screens, with multiple showtimes. I've been told I can create formulas in excel, and the paste them into airtable, but I just can't see a way to do that clicking around. If anyone has any advice, I'd really appreciate it. Thank you!

Hello Airtable community!Hoping someone can assist with this formula conundrum. In short, seeking to calculate 'pending' (sold but not yet activated) subscriptions within an Airtable base. Subscriptions Table primary field is per nominated subscriber record but subscribers are grouped by their organisational contracts which can have multiple subscriptions sold/attached. To arrive at this true subscriptions pending figure I'm thinking the below calculation path:Total #subs contracted (Number field)  -  Status  '_Activated'  (Multi Select field)  -  Status 'Deactivated/Cancelled'  (Multi Select field) = Pending (subs remaining to activate)Is there a way of achieving this within the table or via rollup formula in another table??  (pics attached for elaboration). Status field is currently linked to an activations summary tabl

Hi Guys, I'm working with a project tracker template which has tick boxes for subtasks in one cell. I want to create a column which can calculate how many of those subtasks check boxes have been completed. Is anyone able to help me with this formula?After hours of looking how to do this I'm coming to a dead end!Thanks so much!

I wish Airtable Regular expressions followed some common flavours so we could test it with regex101.comI can make it work in these builders, but then when it comes back to Airtble #ERRROR. It's a huge potential but otherwise Airtable makes it hard to figure out why it isn't working.I am trying to pull out the Patient's name from a Text extract. It always occurs after "Patient's name: " & before "Birthdate:"Example string:Radiology Report Patient’s name: Barney (57493) Birthdate: 7/5/2013 One Regex101.com, and regextester.com I can get it perfectly with this regex:(?<=name: )(.*)(?= Birthdate: )But when I put that in airtable:REGEX_EXTRACT({PDF Text Extraction}, "/(?<=name: )(.*)(?= Birthdate: )") I get a big fat #ERROR I know it's me but can anyone point me in

Could someone help me understand why this formula throws an error? SWITCH(1=1,TRUE,“Y”,FALSE,“N”) Thanks.

I am trying to create the following formula but keep getting an error that there was a problem saving this field. Invalid formula. The intent was if the project is complete or released to customer, then do the followingIf the date diff is negative or NAN, make the result =0 otherwise just do the date diff. However, the NAN is still being displayed. How do I represent NAN value? IF(  OR({🚩Workflow Status} = "Complete",     {🚩Workflow Status}= "Released to Customer"),  IF(      OR(DATETIME_DIFF

Hello!I believe what I'm asking could be a fairly simple fix but been trying to figure out and see if anyone has a formula for this. I created a Task + time tracking sheet for my employees. I'm trying to see if when I put the due date for instance if something is due 04/04/2023 I'd like to see if there's a formula or automation to make a single select field as well to put the month in. So in this instance, the field would be "April 2024" Please let me know if anyone has a solution for this. Additionally, if anyone has any great time tracking/tasks ideas to add to my base please let me know!

hi All, I´m quite new in creating my own bases etc. And I´m struggling with the following. Not even sure Airtable can provide this. The case is the following:I´m working with a campaign which includes multiple types of Activities (Asset Type) and those Activities have multiple versions with unique single ID linked to it. ((and other attributes in the same plan. (Synced by my ID database)). In my Screenshot you can see the data grouped together. All records are linked to the Group Attribute Campaign.  So my question would be, if there is a way to group those records into one data source (Campaign with all assigned IDs) and link it e.g. to another Base into one record? See example attached. This group view would have to be connected as One record. It´s not that easy to explain as english isn´t my mother tongue so please bare with me:)  Looking forward for help

Hi everyone, I would love to have an permanent - Airtable - solution for this problem: the standard Currency format is the US format. Meaning: the COMMA for the thousands and the DOT for smaller figures (lower than $1, for example: $15,388.99). I am from the Netherlands and our system is the other way around. We use the DOT for the thousands, and the COMMA for the smaller figures (€ 15.388,99). What would be the best workaround as long as Airtable does not give me the pick my Netherlands format? Regards, André

I am using `SUBSTITUTE({RENEWAL / NEW MARKETING},",","\n")` formula to convert multi select record to list format as below:Before: After:  As you can see after picture where `Market my products offline (Ads, billboards, trade shows etc.)` is splitting as:`Market my products offline (Ads billboards trade shows etc.)`I want a solution where above formula should not effect commas inside option text?

I have a table called 'Equipment List' and table called 'Projects'.I have a single select field in Equipment List called 'Availability' with 2 options, IN and OUTI have a single select field in Projects called 'Status' with 2 options, COMPLETED and RUNNINGIn the Projects table, I have a linked field to Equipment List, allowing to link to multiple records.The functionality I would like is:IF Status field in Projects is set to RUNNING, then all the corresponding items in Equipment List field, change their Availability field in the Equipment List table to OUT, otherwise set to IN.What is the easiest way to do such a thing?

Thanks to this community I learned how to create nested IF(AND) formulas, but I want to add an additional rule that if another column is No, then it will exclude the amount. Here is my formula. I want to add "IF({PD/PC/PoP Stipend?} = "No", ), "0"), but how would I do that? - thank you!!  IF(   AND(      {New/Refresh} = "New",      {GR/UG} = "Undergraduate"   ),"3,000",IF(   AND(      {New/Refresh}