Formulas

Good day all.I have a formula that currently works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}However, I now need to add an additional IF statement to check for Archived:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&am

I extracted unique names of participants in my table by using the ARRAYUNIQUE formula in a field called {Progress Made BY}. This produced a list of unique names separated by commas in the {Progress Made BY} field, which is what I was looking for. I used this one instead of CONCATENATE because I had a couple of duplicate text strings. I then created an IF LEN SUBSTITUTE formula field called {# Participants Progress Made BY} to count the names separated by commas produced in the {Progress Made BY} formula field. IF({Progress Made BY},LEN({Progress Made BY})-LEN(SUBSTITUTE({Progress Made BY},",",""))+1) *Note that I had used this formula successfully in other tables to do the same thing, however the reference field in the other formula fields was a CONCATENATE formula field instead of an ARRAYUNIQUE formula field. I get an #ERROR message in the {# Participants Progress Made BY} field where there is data (separated by commas) in the {Progress Made BY} field in the record, and nothing retu

Hello!My table is the first step to generate documents with personalized names. Sometime there is 1 name on the document, sometime 2.List of my columns :Name 1Name 2Name 2 + and (formula)I'm trying to find the good way to write the formula of my name 2 + and column : Option 1 : there is a second name and I need « and name 2 »Option 2 : there is no second name and I need a blank field At the moment, if I have no « name 2 » I still see « and » in all my fields 🙂 There is my work/try :CONCATENATE({And}, " ", {Name 2}) (it works fine)IF({Name 2} = BLANK() => don't show "and" in this column. Can you help me fo write this formula please ?Thanks for your reading!Have a nice day.

I'm trying to get this formula to calculate the difference between the date 'today' and the date in the field 'End date', then stay blank if it it's more than 10 days but return 'Yes' if it's 10 days or less. I can't get it to return the right values though. Any suggestions for how I can fix this to get the right result?  IF(DATETIME_DIFF(TODAY(),{End date},'days') >10, " ", "Yes")

Hello!I'm making a table to generate contracts. Sometime there is 1 name on the contract, sometime 2.List of my columns :Name 1Name 2And (just a formula for write "and" in the column).I'm trying to find the good way to write the formula of my and column :CONCATENATE({And}, " ", {Name 2}) (it works fine)IF({Name 2} = BLANK() => don't show "and" in this column.Thanks for your reading!Have a nice day.

I am trying to write an IF function such that the condition in the statement is to look for the presence of a particular word in a multi-select column. If the word is present in the multi-select, the value-if-true should be displayed. In a multi-select column, there is a list of amenities: Airconditioning, Lounge, Studio, etc. I want to create a new formula column that looks for "Studio" in the multi-select column. If it is present then show Yes, if it's not show "No". Something like this: IF(Amenities="Studio", "Yes", "No"). This gives me the correct output if the only value selected is Studio. In the case where the column has "Lounge" and "Studio", I get No as the output. 

Hey dear Airtabler!I have a question about tracking the duration between modifications. A user will type a number in the same field several times a day and I would like to check how long each interval took to use this duration in a formula. How to build such a time tracker? Thanks for all the help in advance! Best, Emmi

I’m setting up a custom ordering form for a family business, where the options that a customer picks will determine some end results like total price, total items, and/or fulfillment time estimates. I’m wondering if there is a way to show a formula field within a form to accomplish this. As a very basic example I might have: 3 number fields 1 formula field that sums the 3 number fields A form that allows users to fill in the 3 number fields I would like for the customer to be able to see the total (formula sum-ing the three number fields) in real-time as they fill out the form, just as I would if I was creating a new record and filling it in inside of the grid view in Airtable. Is this possible or is there another way to accomplish this?

Hey guys,This is driving my brains a bit nuts. I have a base for sales & stock management. In my sales base I have a Date field called INVOICE DATE and a LOOK UP FIELD for the Product's Latest Stocktake.The formula I am trying to achieve here is:IF Invoice Date is BEFORE last stock take (including time) then Result is 'BEFORE'IF Invoice Date is AFTER Last stock take then 'AFTER'IF Invoice Date is AFTER last stock take AND equals or higher than Today+7 days then 'FUTURE'If Last Stocktake is Blank then 'NO STOCK' So far this has worked the closest, although it doesn't have the NO STOCK option and it gets all messed up with I set times and timezones to the date fields.IF({Invoice Date}<{Last stocktake},"BEFORE",IF(AND({Invoice Date}>{Last stocktake},{Invoice Date}>=DATEADD(TODAY(),7,'days')),'FUTURE',IF({Invoice Date}>{Last stocktake},'AFTER'))) Can anyone help, please? 

I'm thinking that there would be a formula to calculate all the days in this pattern described below. But, I just can't seem to crack it.I have a scheduling base that I would like to group by month. But the beginning of this month grouping would need to start on the Monday that is on or before the 1st of the month. This way the grouping includes all 7 days of the 1st week. The monthly grouping would end on the last Sunday of the Month to include all days of the last week. Example: For June 2023... Jun 1st is on a Thursday. So the Grouping of June 2023 would start on the Monday before, May 29th. The grouping would include all rows from May 29th thru the last Sunday of the Month June 25th.Then the next grouping would start. The July 2023 grouping would include all dates from Monday Jun 26th until Sunday July 30th, and so on...The formula would label each row "Year Month#: Month Name" or "2023 06: June". This keeps the groups in chronological order.Thanks in advance if anyone has sug

IF(AND(PHASE="Cancelled",NOT(Notes)),  "Input Re-Exam cancellation reason into the Notes field.",IF(PHASE="No Request Sent",  "Input Re-Exam Packet sent date into the 1st Request field. Take the Due date from the Due field and put on letter."&"\n"&"\n"&"OR update Finalization status to "&"\\""&"Cancelled"&"\\""&" if the Re-Exam will no longer be occuring due to deceased HOH or concurring relocation. Explain cancellation reason in Notes field.",IF(PHASE="1st Request",  IF(AND(IS_After({Due.},TODAY()) NOT({In-Office Date}),{In-Office?}=0), "1st Request is due on "&{Due Expressed}&". Which 

Hello,I want to compare the values of a Rollup field with a Linked Record field.Field A is a rollup that contains the following text:"Value 1, Value 2"Field B is a Linked Record that contains the following records:Value 1 Value 2I want a formula that will check if the two fields are the same. At the moment Airtable is failing whenever a comma is included in Field A. e.g.,Any help would be much appreciated!Cheers

I have a lookup field that I use to pick what Client the new record in my Activities Base is for called ContactsIt auto populates the field record properly when I select the new clientHowever because you can't have the first field contain a Lookup (DUMB BTW especially for rollups and such) I have to put a formula to use the data from the lookup field record to do the rest of my workflow for the clientIt is putting the information in "" (see graphic) (Client field)It did not do that when I just used the old fields of First Name /Last name when I concatenated them (Concatenate field) which used to be the way until we moved into a separate table for Contact informationIs there something I missed in setting up the Account name field or the Contact Field to prevent the " " (quotes) from being shown?

Hi,I am getting this error when I try to upload a string valued date to a "Date" type column in airtable using python script. [Error: {\'type\': \'INVALID_VALUE_FOR_COLUMN\', \'message\': \'Field "Uploaded Date" cannot accept the provided value\'}]Which format will it accept?   

Is it possible to calculate future dates based on the nth occurrence of a day of the week in a given month? For example, I would like to return the dates the 1st Monday in April falls in 2023, 2024, etc.

(sad to see there is no "formatting" category nor a label for this, so posting this within "Formulas")I need to base the currency of my field Goals to reflect the currency of the continent the deal is in.As in "When the deal is in Europe, show the currency as €, however when the deal is in the USA show $"

Hi all,I'm wondering: Do any of you Airtable experts have an idea how I could calculate how many Fridays (or any other weekdays for that matter) there are in a given month?

Dear All,Im trying to create a formula where if an someone starts before the 15th of the month then return the 1st of that month. If they start on the 15th or after then return the 1st of the following month.I have created the below nested IF statement but only the first IF statement appears to be working. For start date equal to or greater than the 15th it is giving me the 1st of the month and not the first of the next month.Please advise  IF(DATETIME_DIFF({Role Start Date},'days'<15),DATEADD(DATETIME_FORMAT({Role Start Date},'YYYY-MM'),0,'months'),IF(DATETIME_DIFF({Role Start Date},'days'>=15),DATEADD(DATETIME_FORMAT({Role Start Date},'YYYY-MM'),1,'months')))  

Hi I have a multiselect that I have parsed out the individual answers to (they are ranked choice votes basically).I want to total the votes, and haven't figured out how to do that though I suspect Rollups and conditional counts could work, this will be a constantly changing tally and constantly updating the linked fields with new voters would be a hassle. This could happen either by talllying the multiselects or the Text fields which have the extracted Individual selections (derived from the multiselect).Currently looking at interfaces is the only straightforward way to do this. Would greatly appreciate any help. Obviously it's doable but seems like itll need an indirect workaround.

HITrying to compare URLs in a Search records module in MAKE.Want to find the 1 record where the URL in the table equals a URL passed from a previous module in MAKE.Can't get it to work.... the formula in AT directly does work, but within MAKE, it simply does not. Added the trim function just in case, tried with the URL as URL field and as text field in AT.... tried with typing = and with the = operator sign in MAKE. Nothing seems to work. Where am I going wrong here?{{if(trim(11.uri) = trim("{Record URI}"); 1; 0)}}Thanks!

I have a formula that worked but I decided to go and mess with it.I have a Date field that is the date of the task actionI have a Days to next action field to add to Date field to define next time to attempt the task (normally a callback)I have a Next Task Date formula field that shows the Date for the next task based on Date + Number of Days  - and it was nicely workingThe formula in the Next Task Date field looked like this - DATEADD({Date }, {Days to next action}, 'days')However I don't want the formula to put anything in the Next Task Date if the Numbers of days is blankHere is my current formulaIf ({Days to next action}=BLANK(), DATEADD({Date }, {Days to next action}, 'days'))It's failing and I can't figure out why.Thanks in advance for helping

Hi everybodyHow could I solve my problem, which is not so complicated, but I am running circles and can not find a solution... I would like to manage installment payment for some of my bills, with fields like Bill, Payment_rate, Rest_of_the_Bill,  The first 'run' is OK, but when I am trying to enter the second Installment for the same Bill, there comes a problem,,, I can not find a way to update the 'rest_of_the_Bill number.Thank you for any thoughts

I don't know if this is possible, but it will save a lot of time if it is. I have a donor database where each donor is known by their full name, ie. "Kate Jacobs". Is there any way that I can add a column with some sort of formula that could just pull the first word from that record? For the purpose of mail merge - I want to be able to do a FName tag, but currently will have to manually go through and rename all the contacts.  

I'm creating an inventory checkout database, based on a sandbox version created by another community member, and am trying to figure out a formula to indicate if items are overdue. Our default check-out time is one business day, and I've managed to create a formula that auto-populates the default due date, and a formula (below) that will indicate if an item is overdue, past that default dateIF(AND({Type}="Checking Out",IS_AFTER(TODAY(),{Due Date})),'OVERDUE') BUT the issue I'm encountering is that sometimes, we extend checkouts in special circumstances. I've created an "Extended Due Date" field, so that staff can manually create an extended due date if needed. However, I'd now like to figure out a formula for this Overdue field that would tell Airtable to first check the Extended Due Date field, and use that if it's populated, and then, if that field is empty, check the Due Date field. How would I do this? Thanks in advance!

I am creating a record keeping system for our market garden. Part of what I am trying to automate is a crop plan that generates a sowing calender - e.g. if tell my system I want to transplant 2x 10 metre rows of Cabbage in April, then Airtable will tell me I need to sow 1.5 trays of Cabbage in mid february in the nursery.I am however having trouble bringing collating data across multiple records. Say that I want cabbage in 4x beds on the same dates in April, each bed may be 0.75 trays of sowing. Because the information is stored against a record which is "Crop + Date + Bed + Row" - there isn't an easy way for me get "oh, I need to sow 3x trays on x date" without manually calculating "4*0.75."I would like to be able to automatically create a record that is a task for x sowing date that would say "3x beetroot"Is there a way to sum a column that is grouped by conditions (in this case, date & crop) in a formula field?AlternativelyObviously I could have a field that specifies how many t