Formulas

Hello,I'm working with the following table in Airtable:My goal is to create another table that simplifies the data. Specifically, I want to generate a single row for each unique ID, where all the text from the 5th field is concatenated into a single cell. Here's an example of the desired output:"Consider installing a longer bath handle. Doesn't completely rest on the bathtub.""Repair chipped tile due to shower column installation. Replace the shower column with a wider one to conceal the defect. Alternatively, replace the tile.""Adjust bathroom cabinet drawer."Important Note: I have thousands of IDs, each with multiple text entries like these. I need to concatenate them to streamline the process of sending email alerts. I've tried various formulas, but I'm a beginner in Airtable.I appreciate any advice or guidance

Hi All,how would I amend the formula below to include Working Days of Monday to Friday and working hours of 9am to 5:15 pm in the out put? INT(DATETIME_DIFF(NOW(), {Credit decision date}, 'hours')/24)&" Days "&MOD(DATETIME_DIFF(NOW(), {Credit decision date}, 'hours'), 24)&" Hours "&MOD(DATETIME_DIFF(NOW(), {Credit&nbsp

I have a complicated string of formulas that is setting due dates. The short of it is I have a "complete" column (checkmark) and "Date completed" (static field). A task is completed and marked with a checkmark in complete where it stays visible all day, then at 11PM, today's date is added to "date complete" which filters it out.  The date complete is the base for a series of formulas in another table which basically determine when to reopen the task as being due again. I've been struggling to figure out why my formulas aren't behaving as expected and I think it comes down to timezone. The most granular failure I can find is a plain formula for "today" - that is the whole formula just "today()" ... the automation pulls the data from that column to drop into date completed and "today" is wrong. I've tried manually setting all date fields to CST, I've tried leaving them all as the standard GMT, I've tried "use same time zone" in formatting an

I have a table that records all of my business leads.I have another table that functions as a report, to provide a summary of all those leads, how much they cost, when they were generated, so on and so forth.These tables are linkedCreated Date column & Created Month column is automated to fill in this linking field when a record is created in the business leads table, to populate the reportHere's the problem, the business looks at weekly data from Fridays to Thursdays. How can I automate the data so that we can look at the report in the week range that is specific to our business?Currently, what I can think of is to create a formula field, {Week Range}The formula would look something like this:  DATETIME_FORMAT( IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Monday', DATEADD(TODAY(), -3, 'days'), IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Tuesday', DATEADD(TODAY(), -4, 'days'), IF(D

Hi folks,Can anyone help my failing Friday brain out please? I have a lookup field pulling through a maximum of 3 payment dates from a payments table. I need to break those dates down across 3 columns; earliest date, middle date and latest date. I have a MIN and MAX in place displaying the earliest and latest values, it's the middle date value I cannot figure out. I don't suppose anyone can solve this with a formula can they? I'm hoping to keep the workarounds to a minimum if possible.Much virtual thanks in advance!

In a lookup field of a record linked to many records, I have a large string of dates. They are in no particular order.e.g:2024-01-09 4:30pm, 2024-01-04 7:00pm, 2024-01-08 2:30pm, 2024-02-09 11:00am, 2024-01-12 6:00pm, 2024-01-12 1:30pm, 2024-01-09 12:30pm, 2024-01-15 5:30pm, 2024-01-12 12:00pm, 2024-01-10 12:00pm, 2024-01-16 5:30pm, 2024-01-18 12:00pmHow would I use a formula to refer to a separate date field(date only, no time), and create a new string with only dates from the large string that match the specified date?e.g. If I specified "2024-01-09", for the data above this formula should return "2024-01-09 4:30pm, 2024-02-09 11:00am,  2024-01-09 12:30pm"Thanks!

I am trying to use this to automatically add 15 or 5 days to the contract date. The “calculation” field is an IF statement to calculate which amount of days to add. BUT I cannot figure out how to get the formula to add the days and give me a new date.

Hi there,I'm trying to create a field in a table (say Table B), where its output is takes into consideration a condition based on a field in Table B, rather than the original table it is rolling up from (which I know Airtable has a simple filter for).In my "Scheduling" table, I want to be able to enter a single date, or a start and end date. This field will look at all Shoots linked to the Team Member that is linked to this record, and spit back out a list of Shoots that have a start and end date/time that is within the dates I specified in the Scheduling table. [Context: This table is linked to a form, and its purpose is to notify a Team Member when their shoots have been scheduled and a short summary of their earliest and latest by email, triggered by an automated button in Interfaces.]The image below shows the structure I've got going now. Thank you!

Hello all,Hope you all had a good holiday season.I have looked, but can't find anything around this, which I think is simple.  Most of what I am seeing on here looks more complicated than it should be.I have a Date Formula field that I would like to populate with Today's date if a box is checked.  I can get that part working, but the rest of the fields show the #ERROR! message.  I just want to hid the message and show nothing.This is what I currently have:Thank you for any help,Matt 

Hi All, hoping someone can help with this.I have a database of clients for content creation, and there are various deadlines for each piece of content we write (for example publish due date, writer instructions due date, draft due date, etc.) We manually enter a publish due date and all other due dates are calculated based on that date using these formulas: DATEADD({Publish due},-10,'days') or DATEADD({Publish due},-5,'days') etc.Recently we've taken on a number of clients from new marketing sources who will have custom deadlines. We have added a column which specifies where the client came from. I'd like to have the dates calculated as above, but ONLY if the client came from Source A.  If the client came from any other sour

I just realised this simple formula, on most fields does not work . Test was done on a Single-Line field. IF(test1 != BLANK(), "Not Blank", "Blank") This will always return "Not Blank".  A workaround seems to be to do the below: IF(Not(test1 = BLANK()), "Not Blank", "Blank") Any idea why this is so? Or is there a simpler work to check for 'Not Blank/Empty'?

Hi, forumla gurus! I’m working on a scheduling database linked to our list of work. I’m a little caught up in the process of flagging when a staff member is on two shifts at the same time. Using @W_Vann_Hall’s formula to count occurrences in a string, using the start datetime and a list of all the staff member’s shift times from a rollup field, I can flag when they have a shift that starts at the same time, as below. IF((LEN(ARRAYJOIN({All Allocated Start Times}))-LEN(SUBSTITUTE(ARRAYJOIN({All Allocated Start Times}),{Start Time},"")))/LEN({Start Time})>1,"Clashing",BLANK()) The next step is adding to the formula so that if the start time is between the start and end times of existing shifts, it raises the clashing flag. Any help in the process would be appreciated!

Hi to all,This is my question:I have a table "table#1" whith a list of records. In this table i have a colum named "project" of type Link with 'allow multiple link', linked to another table "Table#2" with a list of project.In another table "Table#3" I have the same column, named "project", of type Link with 'allow multiple link', linked to the table "Table#2".In "Table#3" I want to create a field to collect and list all the related records in "Table#1" that match this condition: The value of column "project" (in Table#3) is equal to the value of column "project" in "Table#2".Is there a way to do this with a formula?Many thanks.

I have a formula that calculates a 2.99% but since January 1 is 2.29%. Actual formula: ({Total due}-(2.99*{Total due})/100)+{Manual Rate}I have a column: Date Booked How can I modify the actual column named 2.99% to be calculated only if the Date Booked is before 12/31/2023. And a second one to calcula only if Date Booked is after 01/01/2024? Any help, please?

Hi Everyone,How can I arrange chronologically a Month Field (Primary Field also) which is group and has this format 'MONTH YEAR'  ?

I want to make sure the cost of the services is split according to the months it's meant to run. For example, a Product is $16,500 and will run for 6 months (Start in Feb, End Jul), so Product Per Month is $2,750. I want to create a formula that puts a $2,750 in each field (image attached of my table). So in my mind the monthly formula should read: If Start is after 1/31/2023, than equal Product Spent Per Month. The formula that I have in each month is: {Product Per Month} and that's it. I don't know how to make the formula for If, than in this case.

Hi, I have a column with a date and time (starting date and time) in one tab and another column (Duration time) with a duration in another tab. I created a lookup in order to use this duration in a formula to get the endin date and time. I couldn't figure out why my formula is not working...Help ? 🙂

Hi All, I am trying to use the DATEADD formula to add a number of years to a date, The date is in one table and the number of years to add is in another table. DATEADD(Date,{Years till expiry}, ‘Years’) This just returns the same date as the date field. Annoyingly if I remove the {Years to Expiry} and just put a number in there it works fine, But i need to pull a different number of years depending on another field. I am trying to produce an expiry date for a qualification which is linked to a Google drive. the qualifications are listed in another table with the years to expiry number linked to each qualification. once this works i will want to run some conditional formatting in a report to see which qualifications are out of date. I have tried formatting the columns to integer and decimal. Thanks in advance Steve

Hi all! I'm trying to extract the ID from a google drives doc link, any ideas on how to do this? Knowing the ID of the file is the one after the /d/?

I have a field that gets the id from a different line, for example: iddepends on id (void if doesnt)statuscan be done1 In Progressyes21To Dono In this table, each task have an ID.This task might be done only after another task is done.For that, I want to

Hello! I am attempting to recreate some data I have in Google Sheet and improve my work flow. I am currently counting things by hand and it taking to long. What I am trying to do is create a "Master" sheet where all my info lives. But have a "Stats" table that will then automatically aggregate the data by Photographer and month. It would count the specific "Production Type" and sort it by "Photographer" and month. I have attached some screen shots. I have been trying to google it for days with not luck. Here is the start to my "master" tableHere is my current "Stats" tables I have in Google Sheets:By Month:It then feeds into a yearly data sheet

Hi, I have a column where the information is linked to a value in the same tab. I would like the information to be automatically filled with the information on the other line. For example on the screenshot, I would like to have John Doe in the cell for Brandon Walsh when I update the john Doe line.  I tried with the automation but couldn't figure out how to do that.  Thank you very much...

I have a formula for my primary field that looks like this: CONCATENATE({Last Name}, " - ", Type, " - ", DATETIME_FORMAT(Start, ‘MM/DD/YYYY’), “-”, {Job Address}) and it turns out looking like this: Thorton - G, DS, GC, - 08/17/2020- 692 Chateau Ln. Holly Lake, Tx 75461 I would like only the House number of the address to show in the primary field. How do I shorten the address in the formula? Thanks in Advance!

Hi Folks, I'm trying out using the rating field for a ongoing review process and want to be able to roll up an average of those ratings. I've managed to get the rollup to work but can't get rid of the NaN for fields that don't have ratings yet. I've tried a couple different formulas that I've seen for similar questions but nothing seems to be working. Thanks!

Here's what I want to do. I have a field that lists percentages for the fees my company wants to add to a contract. Admin fee 10%, Late fee 5%, etc. If I were to stack these fees, I want to have a field that returns the sum of each percentage multiplied by the contract subtotal field. Is there any way to do this? Like: SUM ( (10% x $800), (5% x $800) ) but where it could recognize the array and do the math for each percentage.