Formulas

Hello! I have an issue with a SUBSTITUTE formula I am using. I started with the following formula: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",{Appraisal Contingency Date} & ""), "{Conditional Approval Due Date}",{Conditional Approval Due Date} & "") It returns the following: Appraisal Contingency Expires: 2022-09-20T00:00:00.000Z Conditional Loan Approval Due: 2022-09-02T00:00:00.000Z Since the dates were not formatted properly, I updated the formula to the following: SUBSTITUTE( SUBSTITUTE( {Email Body} & "", "{Appraisal Contingency Date}",DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY") & ""), "{Conditional Approval Due Date}",DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY") & "") It returns the following:

Hello. This seems like it should be an easy solve but I cannot for the life of me figure it out. I have a table in a base that automatically receives responses from a Typeform survey. It puts the email address (manually entered by person taking the survey) as the main record, and the responses follow in different columns. There is another table in this base where the main record is an employee name, and then we have Department, and 3 columns of 3 different email addresses (there are potentially 3 different ones for each employee, or 2 or 3 can be the same). I have linked the email base to the response base, and as the responses come in, I use a lookup to select the employee and pull in their department. I am trying to get this part to happen automatically. So, when an entry comes in, if that email address matches any of the three emails in the other table, it will bring that employee name in. Hopefully this makes sense and someone has a solution…

Hi, I would like to add days to a start date but exclude Sundays. So if I have a Start Date, I want to estimate the End Date by adding some number of days to the Start Date while excluding days that fall on Sunday. So if I have a Start Date of September 1 and add 7 days, I want to end up with September 9 rather than September 8 as I want to skip over the Sunday in the 7 days. My formula is below: Estimated End Date = IF({Start Date}, DATEADD( {Start Date}, ROUNDUP({Scope}/1000,0), ‘days’), BLANK())

Hello there! I have made an text field where you can enter a date with a “/” or can input only a week. Only problem is now that because the year is in a seperate column it is hard to get a full date when the record doesn’t have a / and get a full date when the record only has an week. Also the timezone in airtable is american by default but i need european.

I have a formula to store progress on a time period. CONCATENATE(ROUND({closed tasks}/{Total tasks}*100,0),“%”) There is another field called “deadline” What i am trying to solve is to avoid an update of the formula after deadline date, in order to “snapshot” how was the progresssion until deadline date. I tried with an IF NOW() > deadline but then the field is blank and nothing is saved. Some idea? Many thanks in advance

Question: I have this formula to pull the latest revision in the Submittal revisions for art work. IF({SUBMITTAL: REVISIONS},SUBSTITUTE( RIGHT(‘’&{SUBMITTAL: REVISIONS},1+ LEN(‘’&{SUBMITTAL: REVISIONS})-FIND(‘https:’,‘’&{SUBMITTAL: REVISIONS},LEN(‘’&{SUBMITTAL: REVISIONS})-133) ),‘)’,‘’ )) It’s an attachment field called Submittal Revisions. I have another attachment field called Previous Stage with an approved attachment file - always filled in with a file. That said, sometimes the SUBMITTAL: REVISIONS attachment filed is empty and doesn’t have any data to pull from. Can we add in that if the SUBMITTAL: REVISIONS is empty, it pulls from the PREVIOUS STAGE file? Thank you!

How do I remove a comma from in front of an email that has been extracted from a rollup. I am very new to this formula writing. I have a rollup field that has two emails in it. I have extracted the first email by itself. However, the second email alway has a comma in front of it. I need to remove that comma. How do I do that? Can I do it by altering this formula in any way? REGEX_REPLACE({Email Roll Up from Educator}, “([^,])(.)”, “$2”) Again, thank you for any help on this.

Hey AT community, I can’t figure out why this formula isn’t working – AT is telling me it’s an invalid formula: CONCATENATE({Full Name copy}, IF(NOT({Full Name copy}), Org), " - $", {Received Amount}, " - ",DATETIME_FORMAT({Date Received}, “D/M/YYYY”)) I want the formula to be [name or org] - {$received amount} - date received Ex. John Smith - $5,000 - 09/02/2022 Thank you!

Hello! I would like to calculate a ‘winning streak’. Each row has the result of a game with a column for wins/losses, as well as a column for the dates of the games. I would like to calculate a winning streak. In Excel, I would do this with a self-referencing formula: IF({Win/Loss}=“Win”,{Win/Loss}+1,0), How might I do this in Airtable? Thanks! Here’s a sample table: https://airtable.com/invite/l?inviteId=invEhMSt3pM1PhMUr&inviteToken=022c846bffe765f7904cc3124920d800b61f879f25b7d41b5030bfabcc64b7bb

Hi, Struggling with DATEADD formula, trying to get an hours bank result from: Date of purchase and days left to use. for some reason, I keep getting the first date I entered- The date of purchase. Appreciate the advice.

Hello! I need help trying to make these comma separated fields into a bullet rich text format and the extra comma’s to be removed at the end if they are present, help please! 55 Cards, 1 Rulebook with 5 Mini-games, 1 Tin, , , , , , , , , , , , Looking for: 55 Cards 1 Rulebook with 5 Mini-games 1 Tin Thank you as always!

Hello, I search on the forum but I don’t find responses. Here is my request : I organise an event with many courses. To do this, I created a form in airtable to allow people to book a place to the course they choose. But I want to limit the number of person for a class and create a waiting list. For exemple : In my form the participant can chose between class A, class B or class C. There is only 6 places for each class. So when six persons are registered for a class, I want to note the 7th person and more as “waiting list”. In my table “Book a course”, I have a field with the quantity of places for a course (“6”), another field with the “autonumber field” to rank person when booked, another field with a formula : when the autonumber of a person is taller than quantity of places for a course, put the label “waiting list” and when this number is smaller or equal, put hte label “booked”. This way, with “autonumber field” it’s OK if I have only o

I’m hoping the community can help me out. I think that my formula is a bit off. Here’s the logic I’m trying to do in the Difference field: If Baseline Value is empty, leave Difference empty If Baseline Value is 0, use the value in Target value If Baseline Value is NOT empty, calculate Target value minus Baseline value divided by Baseline value so I can get the percentage difference As you can see, if Baseline Value is 0 or empty, there’s an error My current formula: IF({Baseline} != BLANK(), IF({Baseline value} != BLANK(), (INT({Target value}-{Baseline value})/{Baseline value}), IF({Baseline value}

I’m trying to set up a date/time field. I’m in Israel and I’d like the timezone to be NYC. My first problem is that when I use CREATED_TIME() to get the current time, the time is off by 3 hours (behind). Then the Set_Timezone function is not calculating properly either.

Hello! Does anyone know how you can transform a week number and a year number to a date?

I need the formula to display the duplicate values found in a rollup. I can’t do ArrayUnique because I actually want to know what the duplicate ones are and there are too many combinations for me to code it using an if statement for each result. Thanks!

Hello! I am new to Airtable and have been chugging along until now lol. I have done hours of research and received advice from a few people but I am still very much stuck. I have a multi select field and I am trying to write a formula that takes the options in that field and replaces/substitutes them with values from other fields. So let’s say my multi select field contains the following values: Buyer Agent Closing Attorney Closing Attorney Assistant I want a formula that looks at each value and replaces each one with an email address from a lookup field. ‘Buyer’ would be {Buyer Email} ‘Agent’ would be {Agent Email} ‘Closing Attorney’ would be {Closing Attorney Email}’ ‘Closing Attorney Assistant’ would be {Closing Attorney Assistant Email} The email addresses would need to have commas separating them. And if there is no email address in any of the lookup fields it would just ignore it. Please help!

Hi! Is it possible to compare single and multi-select fields across columns in one table? Would a formula be able to do this? I have a single select field listing the launch month for content. Then there are 2 lookup fields with the launch months for connected content (each with multiple months selected). Ideally, I want to compare the single select field to the 2 lookup fields and output anything that is after the single select field month. Would these fields need to be dates rather than single or multi-select fields for this to work? If so, is there a way for multiple dates to exist in one field? Or is there another way to do this? Thank you!

Hi All, I’m in the process of creating a catalog of related books that I can hopefully email out to customers, and, so far, that’s involved creating an Entries rollup field in my Catalogs table that pulls from my Books table using the ARRAYUNIQUE(values) aggregation formula. I did as much formatting as possible in the Books table, adding caps with UPPER() and spaces with \n, but I can’t figure out how to eliminate the comma after each rollup field item. I tried this: SUBSTITUTE (ARRAYUNIQUE (values), “,”, " + ") But I kept getting “Sorry, there was a problem saving this field. Please check your formula text.” I’ll add a screenshot here so you can see what I’m talking about and why I’d want to eliminate the comma . . . Thanks in advance for your suggestions!

Hi I would like to change the date format returned in my formula field - I’ve tried using datetime-format but keep getting an error DATEADD({Date Invoice Raised}, 30,‘day,’) DATETIME_FORMAT(‘DD-MMMM-YYYY’) thanks Clare

Hi I would like to add a formula field which calculates when an invoice is due for payment. I have an ‘invoice issued’ date field and would like to use that to calculate +30 days. this is where I have got to but its returning an error DATEADD({Date Invoice Raised} +30,‘day’) many thanks Clare

Hey Guys, first time here. Airtable support forwarded me here. I need your help. I have a such formula: IF( {routine type}=‘daily’, DATEADD({task date}, 1, ‘days’), IF( {routine type}=‘weekly’, DATEADD({task date}, 7, ‘days’), IF( {routine type}=‘bi-weekly’, DATEADD({task date}, 14, ‘days’), IF( {routine type}=‘monthly’, DATEADD({task date}, 30, ‘days’) ) ) ) ) and I would like to combine it with something like this: IF( WEEKDAY({next date})=0, DATEADD({next date}, 1, ‘days’), IF( WEEKDAY({next date})=6, DATEADD({next date}, 2, ‘days’), {next date} ) ) Can you help me, please? The first formula creates new task depending on the routine type. Weekly routine, monthly routine, etc. The second formula should move the task to the Monday if the first formula lands task on the Saturday or Sunday. Appreciate your help. Jack

Hello community I was wondering if you could help me. I want to make a formula that shows me the emoji “ :white_check_mark: ” if the names and emails are in the contact fields, otherwise it shows me “ :x: ” but I don’t know what I’m doing wrong in my formula. Could you help me?

Hello- Any help on not showing negative days here, just show if the number is negative, it says zero? IF({DESIGN DUE DATE}=“”,‘’, CONCATENATE(DATETIME_DIFF({DESIGN DUE DATE}, {TODAY’S DATE},‘days’), ’ Days ')) Thank you!

Hi everyone! This is my first post in the Airtable community, and I have quite the ask. I need to create a formula that is able to look across four different fields in the same table, and check if those fields have one of two particular values (Let’s say the possible values are ‘Complete’ and ‘None’). If all four fields have either ‘Complete’ or ‘None’ at the same time in the same row, I want the formula to print ‘Yes’. If any of the four fields have a value other than ‘Complete’ or ‘None’, then the formula should NOT print ‘Yes’. I have tried using a combination of IF, OR, & AND statements, but this has proven to be quite the formula, and I am not the most experienced coder. My reason for this request is that I want to be able to filter off the value this formula creates, that way I can see all of the tasks in my base that are in progress. I hope this makes sense, any input is appreciated!!!