Formulas

Hoping someone can assist me in creating a formula. Basically I want to take a field that has a numeric value in it and convert it to CM or if there is no number in that field output the text that is in it. e.g.Field A contains value: 60 or contains value "not applicable".Using a formula field I have {Field A} & " in, " & ROUND(VALUE({Field A}) * 2.54) & ' cm'. The output would be "60 in, 152, cm" if it just contained numbers. If that field instead contained text and no numbers I want to just output the text. It won't always be "not applicable" that's just an example. So I figure I might have to use the search() function with regex in the formula with an IF statement. So basically "if "field a" does not contain a number just output text". Just not really sure how to do it! Any help would be great. 

Hello everyone,I'm fairly new to Airtable and I've been impressed with the level of questions the community has solved.  This is the first time I'm posting a question with the hope someone can help me.  Here is my situation:In Table A, I have 3 single-select fields called Trade Choice 1, Trade Choice 2 and Trade Choice 3. Each record (applicant) has a Province of Residence.  I have a lookup field to Table B that lists each trade with the specific provinces where the trade is a Red Seal trade in a multiple-select field called Red Seal Province.I want to add a verification step to confirm when the choices of jobs are entered that they are a Red Seal Trade in the province of residence of the applicant. I would ultimately use condition colour to highlight any choices where the trades don't exist as a Red Seal in the province of residence.How do I write an IF formula that would say:  IF(Red Seal Provinces from Trade Choice 1, Trade Choice 2 

I have a campaign work back schedule. I need to calculate four date fields based on the Launch Date:Assign Due DateDeveloper Due DateQA Due DateApproval Due DateLaunch DateThe number of days for each due date varies based on the combination of two single-select fields:{Channel}: Email, Push, Banner{Send Size}: Large, SmallThe logic is:If Email (Channel) and Large (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -12 days {Developer Due Date} = 6/1, -7 days{QA Due Date} = 6/1, -6 days{Approval Due Date} = 6/1, -7 days {Launch Date} = 6/1If Push (Channel) and Small (Send size) with Launch Date 6/1/23{Assign Due Date} = 6/1, -5 days {Developer Due Date} = 6/1, -2 days{QA Due Date} = 6/1, -1 days{Approval Due Date} = DELETE (or show NA){Launch Date} = 6/1I'm new to airtable so I hope this isn't a noob question. 

Hello! Can someone help me create a formula that finds the first wednesday after the 10th of the month? I have 1 date field that I manually give a date value (eg. 1/4/23), and then I have a formula field that should find the first wednesday after the 10th of the month. Is that possible?

Looking for some help. I have a table were I utilize "last modified time" to be inputed based on the Specific field ("Checkbox").This sequence works well for what I am doing, which is; creating time logs for when we "Start" and "Stop" work on a specific task. I then have a formula set up "Datetime_Diff" where it grabs the difference between the two in minutes (set up as integer) so that I can log billable hrs towards a project (Time on Task).   Here is my problem... I have another table "billing" that I want to utilize a "rollup" field for, to grab the SUM of that data in my "Time on Task" field and have it contribute to a specific job number. I am utilizing this exact same method for and "expense" field but the field the rollup is connected too on that method is a "currency" field (which I cannot use on my time tacking). I am sure there is a solution but I am not an expert.. yet! Any help would be wonderfully appreciated! 

Hello everyone! I have this formula:  IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Dato for udløb}, "D")<10), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"))))) Currently when the field 'Trækdato (FarPay)' is empty, it will give an '#ERROR!'. I wa

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!