Formulas

Inside my client management system, I have three columns:Client status - Active or completed [this changes automatically when client reaches a certain amount of weeks]Weeks - How many weeks they have been inside the program [this has a formula]Start date - when they began the programMy program was 4 months and then I extended it to 6 months.Here is the formula inside the column 'Weeks' that works for 4 months: IF({Days Since Joined}<7,"Week 1", IF({Days Since Joined}<14,"Week 2", IF({Days Since Joined}<21,"Week 3", IF({Days Since Joined}<28,"Week 4", IF({Days Since Joined}<35,"Week 5", IF({Days Since Joined}<42,"Week 6", IF({Days Since Joined}<49,"Week 7", IF({Days Since Joined}<56,"Week 8", IF({Days Since Joined}<63,"Week 9", IF({Days Since Joined}<70,"Week 10", IF({Days Since Joined}<77,"Week 11", IF({Days Since Joined}<84,"Week 12", IF({Days Since Joined}<91,"Week 13", IF({Days Since Joined}<98,"Week 14", IF({Days Since Joined}<105,"

Hey, Is there a formula that pics the name of the month by date. We have built a system where we manage our project invoicing. We send our customers an invoice on a monthly basis based on the work completed, but we try to forecast the billing months by recording the assumed billing month for the project part. So, Airtable should understand that the (billing) month is a date field (billing predictability), but the option value should be the name of the month Thanks Mikko

Hi! I am looking for to have 2 different conditions in one formula. I believe I should be using IF OR but I must be missing something. Here are the formulas:IF(DATETIME_DIFF(TODAY(), {Finish WK 17-24 (Progression)}, 'days')<0, BLANK(), IF( {Start WK 17-24 (Progression)}=BLANK(), BLANK(), IF(DATETIME_DIFF({Finish WK 17-24 (Progression)}, {Re-Entry: Start WK 17-24}, 'weeks')<8, "❌", "✔")))  If date is blank then: IF({Finish WK 17-24 (Progression)} = BLANK(), "❌", "✔")  Please help!Thanks!  

Hi everyone! I need your help with a problem I still haven't managed to solve on Airtable I'm creating an online store where the cart is generated by answering a diagnostic/questionnaire.Each answer to the questions assigns tags (words from the "single select" menu) to the people who answer.Some tags may appear several times.I want to create a program that allows me to count the number of times a tag appears in one cell and rank them from the one that appears the most to the one that appears the least.Then I would like to be able to search, in the "product" table, those that correspond the most to the first 5 tags of the ranking.Thus, I will give the best product to the people answering the questionnaire.(If you also know how to use the TF-IDF (Term Frequency - Inverse Document Frequency) in my case, it would help me a lot)I thank in advance the people who will want to help me with this original request 🙏

Hi, I have 2 tables: Table 1 has a list of contractors and a field showing the value of the projects they've gotten. On Table 2 I have a field showing the contractor with the max value that auto updates as other contractors acquire projects with higher values but I can't figure out how to match the contractor to the value in Table 2. I tried concatenating the 2 fields in Table 1 but then I couldn't figure out how to get that data to populate in Table 2. Thanks!

Hello, I am using airtable to keep track of inventory for my Short Term Rental.I have an "Inventory" table + an "Update Inventory" table.When my cleaner is counting inventory in my STR she'll click on the "Update Inventory" button on my inventory table and a form pops up for her to enter the quantity of an item.After that's done, It'll show the data entry in the "Inventory History" column, as well as all past data entries.How can I show the quantity from the LATEST data entry show in the "On Hand Items" column?

Good evening! I'm creating a scorecard for golfers, everything is going well except, I can't figure out how to calculate their +/- score. I have the total score but I don't know how to add the static numbers of par to each hole so I can formulate the calculation to get the +/-. 

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’), ‘Moderna (First Dose)’, DATEADD(4, ‘week’)) I know that this should be s

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/PropertyProfileOverviewServlet?boro=1&houseno=167 &street=8th Ave &go2=+GO+&requestid=0 There are two problems with this: 1. There are spaces between {Project_StreetAddress} & Report_Link3{Proj_Street_Name}, & Report_Link4 But there aren't any spaces between Report_Link1,{Borough No},Report_Link2,{Project_StreetAddress} and I have not done anything different to the latter part of the formula.2. {Proj_Street_Name} could have spaces like in this example '8th Ave'. If I was to manually get the link from the website it would treat a space as a '+' to complete the link. How can I go about g

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 table,  which totals nominated subscribers activated and those deactivated. Just need a way of calculating true 'pen

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 int he direction of a regex tester that is compatible with Airtable?Or tell me what I can use here?

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