Formulas

Hi, Could you please help me with a formula I have 3 values: Estimated start dateEstimated end dateEffective end dateToday's dateI want to display "in progress", if I have no value in my effective end date cell and if today is inferior than estimated end dateI've tried the following formula but it doesn't work: IF(AND(({Effective end date})=BLANK(), {Estimated end date (Deadline)} > TODAY()), "In progress")Could you help me please? Thanks,

Is it possible to identify if there are any duplicates in a lookup array? I have 3 tables, all linked together: Entities (companies)MeetingsMonthsEvery Meeting is linked to a month (for various uses) and to an Entity. From the Entities table, I have data as such - the linked meetings and a lookup of their linked months. What I'd like to do is identify if a month appears twice in any row. I don't want to eliminate duplicates (i.e. ARRAYUNIQUE). Instead I need to see if 2 or more meetings occurred in the same month for any company. Is there any way to do that? 

I have a field that includes:NameEmailPayment linkPayment_made (checkbox)Created at (Jan 14)Send_reminder_1 (checkbox)Send_reminder_2 (checkbox)If the payment_made is not "check" even after 3 days.I want a send_reminder_1 to be auto-check or something with a formula that will say true.-------If the payment made is not "check" even after 6 days.I want a send_reminder_2 to be auto-check or something with a formula that will say true.-------How can I do this using formula?

I'm trying to create the following automated options in a project management base:2+ days remaining1 day remainingdue today!1 day overdue2+ days overdueThis is my attempt, but I'm struggling to work out how to write the multiple IF statements required to make it happen: IF((ISERROR((ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days Overdue", " Days Left"))))=1,"",(ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days left", IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))=-1," day left", " Days overdue"))))

Hello, I'm trying to merge records in my table when 3 criteria match. I've been able to identify which records are duplicates, but I am having trouble merging them. Logically I believe my code should do what I want it do, I am not super familiar with asynch and I think that is what is messing me up. When I run this code it throws: Syntax error: await is only valid in async functions and the top level bodies of modules [script.js:33:9] I've been reading through documentation and discussion posts, but I'm not sure what is happening to cause it to throw an exception. Any help would be appreciated.   var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); //let records = query.records; let upPN; let upEM; let upID; //identify duplicates let duplicates = query.records.filter((record) => { return query.records.find((potentialDupe) => { let fName = record.getCellValue("First Names") === potentialD

Hi. I have a very basic password system that uses letters to denote numbers. The numbers come from a client's DOB.I have a date (DOB) field and another field using DATETIME_FORMAT to only show the numbers (DDMMYY). That's all fine.I would like to have a new field that displays a particular letter for each number - to create a very simple password. Lets say: 1=a, 2=b, 3=c, 4=d, 5=e, 6=f, 7=g, 8=h, 9=i, 0=jIs there a formula that would do this please? 

My airtable has two fields, for example, 1st field name is "Test" and 2nd field name is "Solution". Records from both fields are come from Lookup of other fields so multiple records appear in "Test" and "Solution". i.e. one record of 'Test' contains 'Test item 1, 2 & 3'. One record of 'Solution' contain 'Solution item 1, 2, & 3'.I use below formula to present Test item 1, Test item 2, Test item 3 in separate lines under one record of  "Test" fields. Solution item 1, Solution item 2, Solution item 3 in separate lines under one record of "Solution" field. SUBSTITUTE( ARRAYJOIN({Your Lookup Field}), ",","\n" )However, I would like to present the information in pair but separate line in NEW FIELD?  e.g. 1st line - Test item 12nd line - Solution item 13rd line - Test item  24th line - Solution item 25th line - Test item 36th line - Solution item 3Would anyone propose how to do it? Thank you. 

I have a project stage field that I need to convert into generalized phases. I have the below SWITCH formula and I cannot get it to save. All of the articles I have read have examples formatted this way - What is the issue? SWITCH(  {Project Stage},   ’Discovery’, ’In Progress’,   ’Planning’, ’In Progress’,   ’Account Build/Import’, ’In Progress’,   ’Execution/Migration Prepped’, ’In Progress’,   ’Imported’, ’In Progress’,   ’Import Prepped’, ’In Progress’,   ’Complete - Migrated’, ’Complete’,   ’Migrated/Monitoring’, ’Complete’,   ’Project Closed’, ’Closed’,   ’Pending')

Hello! I have checked out the the community for an solution to my problem and can't find what I am looking for to help me with my formula and automation. I am trying to set-up an automatic alert announcing someone's birthday on the day of their birthday. It did work but it sent the announcement the day before as well as on the day of the persons birthday.This is my formula: DATETIME_FORMAT({🎂 Birthday}, "MMDD") = DATETIME_FORMAT(TODAY(), "MMDD") My automation is set up for when a record updates, it sends an email on the day of the persons birthday. However, it sent an email the day before and the day of.I only need it on the day of the actual birthday. Can someone tell me where did I go wrong?Thanks in advance.

Hi there, I am trying to create an If/or formula and got it to work but I can't get the formula to be blank if there's no data in a field. I'm sure that makes no sense. Here's an example: Let's say I have a small list of produce in a column. I want to create a formula in a new column to give me the category of produce either as a fruit or as a vegetable. I got the formula to work but if no produce has been selected I'm still seeing "Vegetable" in the formula column and I want it to return a blank value. Any ideas? Here's the example formula. Thanks in advance!IF(OR({Produce}="Grape",{Produce}="Apple"),"Fruit","Vegetable") 

I have a large multi-select that I then need to turn into a list format. I did the usual substitute the comma for a line break but unfortunately the items in this multi-select (that I can not edit) also use commas so now it's adding commas where I don't want them. Is there a way to get the formula to avoid those commas but not the others?  SUBSTITUTE({Plot Themes},", ","\n")

Please see screenshot below. I have domain names in one field and want a clickable "click here" text in another field that hyperlinks to the domain name, how can I do this? Thank you 

Hi all!I've been scratching my head and couldn't solve the problem quickly, so I'm asking for your help.I need to display a progress bar with the percentage of completion of a specific task, under which there are subtasks. In order to understand the percentage of task completion, we take the subtask as the source. If out of 10 subtasks only one is completed, then we get 10% of the main task completed, a certain run rate.It looks like a similar task can be implemented through a formula, but I was unable to write a correct formula using if, counting and division.How can this case be solved?

I want to create an Project Management Table with the following requirementsI have several project stageseach stage has a start date and an end date (automatically calculated based on duration for each project.)the start date of one stage should be dependent on the end date of the previous task. So if "task A" ends on July 5th, the start date of task B should be the 6th. The end date of task B should be start date of task B + duration,... and so onso whenever something changes, the following dates should change as well. (also reflecting the dedicated duration set for each task)I tried with formulas or dependencies but could not figure out a way to automate this, instead of updating all records manually if one date changes.ThanksHelenelene 

I'm trying to follow this guide, but everytime I reach the "Concatenate step" the link that shows up to me says "The private share link you tried to reach is not available.".That's a copy of the table I'm using to, why it doesn't works and how to fix it?https://airtable.com/appFaino3o1b2cqEG/shrHoTe908izs7JPn

I have rollup field that sum all the records from a table. I want to limit the sum from all to sum for current month only.How can I do it? 

Looking for a solution for a public view where I would like to share information partly. To be exact - I'd like to hide the last characters of the text string by replacing the last letters with stars ***Tried to look up for ready solutions but had no luck.Would appreciate any help.

Hi. I'm sorry if this sounds a bit vague, but what I'm trying to do is this:I conduct sessions with students and each session is a record. At the end of a month I have a table with multiple students and their sessions. Some students will have 4 or 5 sessions per month.What I want to do is find all sessions for each student, then extract the 'date' and 'duration' fields data to make a list of all the sessions the student has had in that month. I will then use an automation to email this information.I'm not sure where to start with this one. Is there a nice, elegant way to do this? 🙂

I need to be able to total up sums per line from an original start date for a full year after.Example:  Original  start date                   Amount paid-1, amount paid-2, amount paid-3, etc.                  This would be calculated for 1 year, then we would have a new date to calculate the 2nd year.                 Each customer will have a separate start date so the formula would have to be connected to the start date column.              

Hi,I have a currency column that is being auto-calculated, and the way Airtable is summing the numbers is sometimes incorrect - off by a few cents.I suspect this is because the auto-calucations sometimes come from .00 or .000 fractional ownership of certain goods, so when Airtable sums a bunch of those at once it's somehow dropping or carrying over fractions of cents...If I copy and paste the whole list of numbers in Excel, for instance, it gives me the correct total. So I'm searching for a way to somehow convert the auto-calculated field to a plain number field which will hopefully only give me the sum of the numbers as they are.Any suggestions for how to to this? Thanks!

I have two tables: "Billing" and "Activities". I'm trying to find a way to combine this data so that I can see $ received from bills while only including "TimeEntry's". In this specific scenario, the amount I would need to see is $530, since the $100 is an ExpenseEntry.It is way too time consuming to manually count the hundreds of records, so I need a formula or an easier way to compute this. Thank you very much!

Hello everyone,I can't create a formula to find pieces of text in the "Libellé" (Text) field and transform them into a word.Example :IF “PAYMENT” or “PRLV SEPA” in the "Libellé" fieldTHEN “VER” in the formula field

Hello.I need to calculate the average of the entire column (called total score) but using the average formula I am getting the average of each cell. Is there any solution please?Thanks

Hello,I have been googling this for a few days now and really need some help!I am trying to set up an existing customer email reminder automation that uses date the next time we are going to visit a venue. The trigger will start a month or so before the visit date. A new customer fills out a form and a record is created. This gets linked to our Class bookings table with the venue that they have visited. The bookings table is linked to a classes table which shows each venue and the 4 dates we visit that venue. So far i have managed toLookup the dates from Classes in the class bookings table - this gives me an entry with all 4 dates in the same cell. The format of this row is a date. (perfect)In the customers table i am able to use a rollup to get this date across and then use an ARRAYSLICE to get the individual dates i need. They come in a really long format with the time 2024-02-01T00:00:00.000Z. I can clean this up using some variation of DATETI

What can I use in a formula field to copy the date from one field to another without changing it into a text string?For context, I have install dates and site visit dates in the same table, and the dates are in the same column for 'start' and 'end'. I am setting up 4 more fields (Site visit start, site visit end, install start, install end) that will remain blank if the date type does not match. However, I can't find any way to keep the date as a number/ date format and not a text string? I basically just want to copy a date (unchanged, not adding or subtracting days or I could use DATEADD), but the only one I can find is DATETIME_FORMAT but this changes it into a string. Can anyone help? I understand I could use automations to copy the date, but I'm trying to limit my use of those because I am nearing the limit of automations per base! So a formula would be much better if possible. Code I'm currently using is below:IF( {Date Type} = "Install", IF( {End} != 0