Formulas

In the theatre world our "fiscal years" are actually seasons that start in the fall and end in the spring.  And specifically, there are two big "quarters" = a fall/spring season, and a summer season. More complex than just "quarter 1" plus "year." and I don't think I can use the MONTH function.I want to be able to use an if then statement that I can set once and forget it without having to create a new formula field when each new season rolls around.If the date range is between 09/01/(yyyy) - 05/31/(yyyy+1), it's displayed as the "(yyyy)/(yyyy+1) Season"Ex:  Engagement end date is 09/02/2022.  It would be displayed in the column as "2022/2023 season"If the date range is between 06/01/yyyy - 08/31/yyyy, it's "yyyy Season"Ex: Engagement end date is 0701/2023. It would be displayed in the column as "Summer 2023 season" Is this possible??M

I am trying to create a payment due date based on adding days (either 15 or 30) depending on what kind of vendor type is selected. Both 'if' parts of the formula work if I place them in separate formula fields but I would like this to all come through on the same column so I can see 'payment due date' in one place - anyone able to help?IF({Vendor Type (from Vendor) 2}=“Independent Contractor”,  DATEADD(Created,3,‘days’),IF(  {Vendor Type (from Vendor) 2}=“Included in Payroll/TEAMs”,  DATEADD(Created,5,‘days’))

Hi, I'm trying to create a formula that will direct to a Gantt view filtered by a ProjectID field (So I can create a "Open Gantt Chart" button in an Interface using the "go to url in record" action that will open a Gantt view filtered by a specific project.) I can create a formula that generates a filtered link to a Shared View (https://airtable.com/shrXXXXX?filterEquals_ProjectRecID=XXXXXXX)  but this Shared View isn't editable, which defeats the point. I have tried a formula for the non-shared view URL (https://airtable.com/appXXX/tblXXXXX/viwXXXXX?filterEquals_ProjectRecID=XXXXX ) but that only opens the un-filtered view. Are there any tweaks to the URL formula that would make this work? Thanks! [Slightly off topic rant - I'm working on creating a semi-complex workflow Interface, and I'm getting increasingly frustr

Hello! I'm creating an Airtable form, and I want to enable a default text to fill in a field if the field is left empty by whoever is filling the form. Example if name is not filled in, then it will default to anonymous instead of being blank. How do I go about doing this? I tried to use the blank formula myself but I failed :')Thank you so much!

Hi all, I would like to extract an ID of a link with Regex formula but I don’t find how this formula works. Example: Link Drive (false link) https://drive.google.com/drive/folders/E1x1a1m1p1le1 And I would like to extract this part: E1x1a1m1p1le1 (i.e. part after “folders/”) I have tried this formula: IF({Link}, REGEX_EXTRACT({Link}, “folders/[^\s]+”)) but I have the following result : “folders/E1x1a1m1p1le1” instead “E1x1a1m1p1le1” I guess it should only be a small adjustment. Could you help me? Thank you in advance, Sébastien

Hi Folks, I'm currently struggling with a formula. I'm trying to track the progress of a project that requires an asset to be translated to several languages. I created checkboxes for each language and for when those assets were QC'd. I'd love to be able to consolidate all the checkboxes to a single text field so I can easily see which languages have been completed and QC'd. I've been able to create a formula to pull a checkbox per language but now I'm adding the second step of QC and my IF AND formula isn't working. Below is a sample of the formula that worked and the one that isn't once I added the second step. TRIM(IF(({BR Art}),"BR, ")&IF(({DE Art}),"DE, ")&IF(({FR Art}),"FR, ")&IF(({IT Art}),"IT, ")&IF(({JP Art}),"JP, ")&IF(({MX Art}),"MX ")) Th

Hi all,For about a month, I have noted that when I create a new table and the Primary field , which I have as a formula set to pull it from from the "Name" field, doesn't automatically self populate. So if i create 1-2-3-4-5 new tables, with individual's names, when I look at the new entities in grid view I can peek at the data but the first primary field to the left is blank until I refresh the URL. Then it shows the details. We have a Base with about 4500 tables.  Not sure if that changes anything or if this is a result of the size of our base.  

How can i create a field that reads, for example, "Friday March 17, 2023" ?

Hello All,I am trying to figure out how to perform a calculation if possible.I have jobs that are sorted by date. Currently, data is grouped by date. I want to take the flat rate for example $500 and divide that by the number of jobs. So that I can have a column that tells me how much each job was worth at the flat rate. So if there were 14 jobs and the flat rate is $500 each job for that day will get its portion of the 500. The number of jobs per day can vary. For example, on some days, I will only have 6 jobs but the same flat rate. In addition, I would like to know how many jobs are urban and how many long distances. I already have the data using a lookup by zip code. Just need to know the percentage of revenue that is urban and long-distance. Hope this makes sense. Been trying different formulas but cannot figure how how to get the count working. I appreciate any help or advise you may have. Thank you so much

I imported a financial report from PayPal. All of the Negative amounts in the "Amount" field use a "-" in front of the value. I want to create a new field that removes the "-" and just lists the value of the Amount field.  If the amount doesn't have the "-" symbol, I just want it to leave the record blank.  I tried using a substitute formula in the new field, but not sure how to get a blank value in the new field if the original amount value does not have a "-" sign. SUBSTITUTE(Amount, "-", " ") What would be the best way to do this? Thanks in advance.

I'm trying to build a formula that returns "true" every 180 days (and subsequent 180 days) after a record was created.  I'm heading down a very complicated route involving dividing  {Days Since Created}/180 and checking the length of the return result.I'd like to be able to build a "divides exactly" check into this:DATETIME_DIFF(NOW(),{Date Created},'days')Any ideas would be gratefully received.

HII have  "Creation Time" column for my records. The records are created when user submits the form.Everyday at 9 am or 10 am in  morning meeting, i need to show all records created between  " Yesterday 8am to Today 7:59am".Is there a formula to achieve this?I am not  good at formulas to if any one create one for me please.Regards 

Hiya, Basically want to know if there is a formula where if the tickbox "marketing brief" column is filled in it will automatically then remove it will remove the marketing brief option from the multi-select "outstanding" column? I've been looking on the forum and can't quite find anything similar. Thanks!  

Hello everyone, I'd like to make a formula with an IF statement that shows "OK" if a date is in the last 6 complete months. For example, if today is the 17th of March, it would show "OK" for every record since the 1st of September.Is this something possible?Thanks 🙂

Is there any reason for -0 in the result of the simple formula as Paid-Shipped? Is it interesting, as it shows only in one row.  

Hi! I need help creating a formula and not sure if it is possible. I have a table that has several tasks with a single select option of completed, missing, or n/a. I want to get the average score of the tasks that are marked as completed. Example: Task 1 is completed, task 2 is missing, task 3 is completed, and task 4 is completed. The average would be 75%. Or if Task 1 is completed, task 2 is missing, task 3 is missing, and task 4 is N/A. The average would be 25% and so on.  

Hi I'm a Plus plan subscriber so starting to explore AT more in depth. I have a built a base with a JOBS table that lists jobs by job number, and using various currency, numerical and formula fields, delivers a profit per hour figure for each job. It does this by dividing the total job profit by the number of hours spent on the job. A second table called TIME LOGGING is updated with forms submitted by staff and tags their time to a job. These individual time logs are rolled up in the JOBS table to deliver the profit per hour figure. The next part is where I need help. How can I now deliver a PROFIT PER HOUR PER STAFF MEMBER figure which takes into account that time they have spent on each job? On some jobs, several members of staff log time, so the figure must be proportional to the time they spent on the job. E.g. If the staff member logged time to two jobs, one of which was just them, and one of

I am trying to build a base for a company that delivers natural food for pets. The clients tells us the date they want their first delivery made, how often they want to receive it (every week, every 2 weeks, etc), the preferred day of the week they want to receive the deliveries (monday, tuesday, etc) and also the number of deliveries that are already paid for. Based on that I would like the base to automatically fill the future delivery dates for each client record considering the informations we have. This will be importante so that I can set up a calendar view later that will allow me to know all the clients I have to make deliveries on any specific date. Thanks in advance for any help and I am sorry if this is not the right place to ask.

Subrecords is the one aspect I don't like in Air Table and I am struggling with a case right now. Maybe somebody has a smart and simple solution. I feel my design is flawed but I did not find any other practical way to work this.Let's say I haveTABLE AField 1: Location name (text)Field 2: Sub-location name (text)Field 3: [Field 1] & [Field 2] (calculation field)Field 4:: A chekmark field to filter out sub-records, so I can a list of records without seing all the time I repeat Field 1.TABLE BField 1: Area code (text)Field 2: [TABLE A Field 3] (linked) What I would like is a list of unique records of Table A Field 1, with all the linked records in Table B Field 1.And I can't seem to find a simple solution. 

I am trying to figure our how to use rollups. Let's assume I have a list of applicants and associated interviews. The interview has "interviewer name" field.Soapplicant1 -> (interview1; interview2)Interivew1: JohnInterview2: RebeccaI am creating a roll-up and trying to figure out how can I access interviewer names (John, Rebecca).Rollup references gives some formulas that can be used in rollups, but is there a way for me to access the values?Let's say I want to send an automated email and sign it with the name of one of the interviewers. 1. SQL allows me to do something like ANY() to get a random name from the list of interviewers. Is there something similar in Airtable?2. What if I want to SORT BY interview date and access the name of the FIRST interviewer? (so not just any, but also do

Hello communityAgain faced with this, hope to get a simple workaround for this where I can get a formula that extracts URL from a text field. Thanks

Hi everyone,I need assistance with getting the proper year in an ISO week start date so that I can get a valid week start date. I have the following formula: DATETIME_PARSE(DATETIME_FORMAT({Date Field}, "W"), "W") Month and day outputs from this method are accurate, but a year is not. It is stuck in 2023, the current year. Can somebody assist me? It's acceptable if I need to use more than one field to accomplish this, for example, one field to obtain the proper year and one field to obtain the correct day.Sincerely appreciated! 

We have multiple dates that we need to have automatically update for us in table.Example:Move-in date (4/1/23) plus policy day (60) = Lease end dateLease end date - NTV date (30) + 5 days= NTV date needArrival Instructions date = 2 days prior to Move-in date (4/1/32)How would you go about doing this in a table? 

Hi, Using the formula below I have an ERROR value issue when there is no date value available for {last deal stage change}. Can anyone advise how I would alter this to just leave a blank space or "no data" where there is no date value available in the date field column?I need to use the datetime format to tidy up and remove the timestamp TRIM({Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & DATETIME_FORMAT({last deal stage change},'DD/MM/YY') & " | " & {Deal Stage})Thanks!

Hi Community, I have 5 fields (checkbox) that can be selected or not, I need to assign a status triggered by these fields, if any field of the 5 is selected then a status will be set and if no one is selected It will assign a different status. Thanks for the help.